5 Replies Latest reply on Jan 14, 2019 10:05 AM by Joe Oppelt

# LOD calc field

Hi all,

Looking for some help here, I'm pretty sure it's a LOD calc (I could be wrong) that I'm needing help with. Below you'll see a simple example of the problem and attached is the workbook I'm working with.

I'm looking to obtain monthly attrition rates, I have monthly headcount and monthly terminations but I'm needing to create a calc field that will produce the avg. headcount for each month and a number value for each month to complete the formula in tableau.

Attrition rate formula: (monthly terminations/avg. headcount)*12/month number

Report Month
Month Number
Attrition Rate
Oct161341191613419% (119/16134)*12/1
Nov161931141616324% (114/16163)*12/2
Dec161132141614635% (214/16146)*12/3

In the attached workbook I have the first three columns (report month, headcount, monthly terminations) but do not have the last three, was hoping I could get some help on this.

• ###### 1. Re: LOD calc field

Hi David,

Here it is:

Thanks

Deepak

If it Helps, Pl Mark it Helpful and CORRECT to Close Thread

• ###### 2. Re: LOD calc field

David -- Notice that Deepak's example has no LODs in it.

I did a different approach that also used table calcs, but the principles are the same so I'll just leave Deepak's solution here.

You need a table calc because you need a running value of some sort to get the average headcount.  (I did RUNNING_AVG in mine.  Deepak uses a WINDOW_AVG that uses a RUNNING_SUM in his.  There are multiple ways to do these things.)

Notice too that Deepak broke apart [Counts] into separate measures.  I did the same.  It helps with the display of values.  Using [Metric(group)] on the sheet creates a structure that is awkward for displaying the final number you need to see.

• ###### 3. Re: LOD calc field

Hi Deepak,

Greatly appreciate the calc field(s) you helped me with. Is there any way to apply the department filter and flip between departments, it didn't seem to work when I tried it.

• ###### 4. Re: LOD calc field

Yes you can,

Thanks

Deepak

If it Helps, Pl Mark it Helpful and CORRECT and Close Thread

• ###### 5. Re: LOD calc field

I missed the LODs in Deepak's example that are used to break out Headcount and other measures.  That's why you need CONTEXT.

You can also break them out just like this (example, for HEadcount):

if [Metric (group)] = "Headcount" then [Counts] END