Suming two Columns from Different Tables - With Same Column Name

[Mavericks334]

Hi,

I have Different Tables, 1. Voluntary Attrition. 2 InVoluntary Attrition. 3 Business Units

Each had dept ID' Column, Month Column. Below table is one for Voluntary Attrition.

[TABLE="width: 242"]
<COLGROUP><COL style="WIDTH: 50pt; mso-width-source: userset; mso-width-alt: 2450" width=67><COL style="WIDTH: 48pt" span=4 width=64><TBODY>[TR]
[TD="class: xl65, width: 67"]Dept Id[/TD]
[TD="class: xl66, width: 64"]Oct'09[/TD]
[TD="class: xl66, width: 64"]Nov'09[/TD]
[TD="class: xl66, width: 64"]Dec'09[/TD]
[TD="class: xl66, width: 64"]Jan'10[/TD]
[/TR]
[TR]
[TD="class: xl67"]3310ED[/TD]
[TD="class: xl69, align: right"]1[/TD]
[TD="class: xl69"] [/TD]
[TD="class: xl69, align: right"]1[/TD]
[TD="class: xl69"] [/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]3310FL[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl67"]3310HD[/TD]
[TD="class: xl69"] [/TD]
[TD="class: xl69"] [/TD]
[TD="class: xl69"] [/TD]
[TD="class: xl69"] [/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]3310PZ[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[/TR]
</TBODY>[/TABLE]


Similar table for involuntary attrition. I need to combine the both the Tables and get a consolidated number according to the month and Dept Id.

Regards,
Renato.

 

[Mavericks334]

I tried that, it does not work, because it is two different sets of data and i am unable to create a relationship to sum both the tables us because over a period of time the dept ids repeat. The Volunatary termination table and involunatary termination table need to be added together 1st, i get an error when i try to create a relationship between them because i nee to sum the numbers of a particular month together to get the overall attrition.

 

[Laurent C]

i am unable to create a relationship to sum both the tables us because over a period of time the dept ids repeat. QUOTE]

You cannot link one table to the other. You must link both tables to your dimension table.

 

[Mavericks334]

i have tried that option as well. I have linked both my tables to my Function table and still i am unable to add both these columns

Main Table { Dept Id, Dept Name, Function}, Voluntary Table { Dept Id, Month, Attrition}, In Vol Table { Dept Id, Month, Attrition}

The relationship is between the dept id in the main table since it is unique. In the Voluntary Table and invol table the dept id's have attrition values for each month below is the details of both the tables.
Vol Table[TABLE="width: 152"]
<COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><COL style="WIDTH: 48pt" width=64><TBODY>[TR]
[TD="class: xl63, width: 64, bgcolor: #4f81bd"]Dept Id[/TD]
[TD="class: xl63, width: 75, bgcolor: #4f81bd"]Headcount[/TD]
[TD="class: xl63, width: 64, bgcolor: #4f81bd"]Attrition[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: #dce6f1"]3310ED[/TD]
[TD="class: xl66, bgcolor: #dce6f1"]10/1/2011[/TD]
[TD="class: xl64, bgcolor: #dce6f1, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]3310FL[/TD]
[TD="class: xl67, bgcolor: transparent"]10/1/2011[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: #dce6f1"]3310HD[/TD]
[TD="class: xl66, bgcolor: #dce6f1"]10/1/2011[/TD]
[TD="class: xl64, bgcolor: #dce6f1, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]3310PZ[/TD]
[TD="class: xl67, bgcolor: transparent"]10/1/2011[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: #dce6f1"]3310RC[/TD]
[TD="class: xl66, bgcolor: #dce6f1"]10/1/2011[/TD]
[TD="class: xl64, bgcolor: #dce6f1, align: right"]0[/TD]
[/TR]
</TBODY>[/TABLE]
Invol Table[TABLE="width: 152"]
<COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><COL style="WIDTH: 48pt" width=64><TBODY>[TR]
[TD="class: xl65, width: 64, bgcolor: #4f81bd"]Dept Id[/TD]
[TD="class: xl65, width: 75, bgcolor: #4f81bd"]Headcount[/TD]
[TD="class: xl65, width: 64, bgcolor: #4f81bd"]Attrition[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #dce6f1"]3310ED[/TD]
[TD="class: xl68, bgcolor: #dce6f1"]10/1/2011[/TD]
[TD="class: xl66, bgcolor: #dce6f1, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]3310FL[/TD]
[TD="class: xl69, bgcolor: transparent"]10/1/2011[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #dce6f1"]3310HD[/TD]
[TD="class: xl68, bgcolor: #dce6f1"]10/1/2011[/TD]
[TD="class: xl66, bgcolor: #dce6f1, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]3310PZ[/TD]
[TD="class: xl69, bgcolor: transparent"]10/1/2011[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #dce6f1"]3310RC[/TD]
[TD="class: xl68, bgcolor: #dce6f1"]10/1/2011[/TD]
[TD="class: xl66, bgcolor: #dce6f1, align: right"]1[/TD]
[/TR]
</TBODY>[/TABLE]

These both need to be added to get[TABLE="width: 152"]
<COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><COL style="WIDTH: 48pt" width=64><TBODY>[TR]
[TD="class: xl65, width: 64, bgcolor: #4f81bd"]Dept Id[/TD]
[TD="class: xl65, width: 75, bgcolor: #4f81bd"]Headcount[/TD]
[TD="class: xl65, width: 64, bgcolor: #4f81bd"]Attrition[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #dce6f1"]3310ED[/TD]
[TD="class: xl68, bgcolor: #dce6f1"]10/1/2011[/TD]
[TD="class: xl66, bgcolor: #dce6f1, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]3310FL[/TD]
[TD="class: xl69, bgcolor: transparent"]10/1/2011[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #dce6f1"]3310HD[/TD]
[TD="class: xl68, bgcolor: #dce6f1"]10/1/2011[/TD]
[TD="class: xl66, bgcolor: #dce6f1, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]3310PZ[/TD]
[TD="class: xl69, bgcolor: transparent"]10/1/2011[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #dce6f1"]3310RC[/TD]
[TD="class: xl68, bgcolor: #dce6f1"]10/1/2011[/TD]
[TD="class: xl66, bgcolor: #dce6f1, align: right"]1[/TD]
[/TR]
</TBODY>[/TABLE]

Then would divide it by the total headcount, i am unable to add these two columns to get that info. Do let me know incase you need more info

 

[Laurent C]

You could certainly create a calculated column, but why not just create a measure to calculate total attrition?

 

[Mavericks334]

That is what i have been trying to do, and have failed. The overall attrition is the one where i am facing the proble, because i need to combine both vol and invol attrition. I am looking for answer for the same. so i could start calculating the moving averages based on this

 

[Laurent C]

Can you describe your attempt, and the steps you took to create this calculated measure?

 

[Mavericks334]

=IF(HASONEFILTER(BU[Dept Id]),CALCULATE(VALUES(TERM[Month]),BU)&CALCULATE(VALUES(XOFT[Month]),BU))

Above is the measure i tried did not work out properly for me.

 

[Laurent C]

Are you trying to return a month name? What are the TERM and XOFT and BU tables?

Main Table { Dept Id, Dept Name, Function}, Voluntary Table { Dept Id, Month, Attrition}, In Vol Table { Dept Id, Month, Attrition}

[Total attrition] :=
SUM(Vol[Attrition]) + SUM(Invol[Attrition])

Depending on how you what the date span should be, you might need to use to Time Intelligence functions to get the right one.

 

[Mavericks334]

the Date is either the month end of each month. ie 10/31/2012 or 9/30/2012. Term is for terminations xoft is another kind of terminations. Bu is the main table.