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

    LOD calc field

    David Lopez

      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
      HeadcountMonthly Terminations
      Avg Headcount
      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
          Deepak Rai

          Hi David,

          Here it is:

          Thanks

          Deepak

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

          • 2. Re: LOD calc field
            Joe Oppelt

            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
              David Lopez

              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
                Deepak Rai

                Yes you can,

                Just add it to CONTEXT.

                 

                Thanks

                Deepak

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

                • 5. Re: LOD calc field
                  Joe Oppelt

                  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