1 Reply Latest reply on Aug 27, 2018 3:47 PM by Patrick Van Der Hyde

    LOD Calculations

    Deepika Siriah



      I am working on this headcount analysis. I have calculated fields based on my data for the same as below:


      Headcount - IF [Status]= "A" OR [Status]= "L" THEN [Ee #] END


      Terms -

      INT(IF { FIXED [Ee #], DATEPART('quarter', [Quarter End]), [Event Date] : MAX([Event Date])} = [Event Date] THEN

          IF [Event Type] = "Termination" THEN [Ee #] END END)


      However, but I am unable to write the LOD calculation formula for hires. My attempt for the same was:


      Hire -

      INT(IF { FIXED [Ee #], DATEPART('quarter', [Quarter End]), [Event Date] : MAX(DATETRUNC('quarter',[Event Date]))} = [Event Date] THEN

          IF [Event Type] = "Hire" THEN [Ee #] END END)


      However, the numbers for hires per quarter is incorrect. My event type = "hire" contains existing employees, new hires as well as hire date of termed employees. So, I am finding it difficult to write a formula which would just give me hires for the current quarter from the event type = "hire".


      Attaching my workbook for the same. Help appreciated! Thanks!

        • 1. Re: LOD Calculations
          Patrick Van Der Hyde

          Hello Deepika,


          I have looked at the data but I am not understanding the logic of the data to determine if a "Hire" in Event Type should be counted for a unique EE # or not. 


          Also, please move EE # to the Dimensions since this is a Dimension value and not a measure, it will be easier to work with as it is dragged to the views.