1 Reply Latest reply on Apr 16, 2018 12:22 PM by Yuriy Fal

    Max Headcount for a Period

    Anupam Singh

      Hello Everybody,

       

      Have been trying to achieve this in several ways but the performance seems a hindrance. Any inputs are highly appreciated. Trying to get the sum(Headcount) in an organization based on the Date. So we have data for every day at each employee level in the table.  Consider a dataset like this for 365 days, with about 80000 employees. We need to fetch the Max Headcount based on the last date of Time Period selected. If Year(Date) is selected, it needs to fetch the sum(Headcount) as of 31 Dec 2018; if drilled down to Quarter, the sum(Headcount) should show up the numbers as of 31 Mar 2018, 30 Jun 2018, 30 Sep 2018, 31 Dec 2018; if drilled to Month it should be the number of only the last date of a month.

       

      If(Date = MAX(Date) then sum(Headcount) is the one working but with a lot of performance hit.

      Tried LOD with {Include Date:Max(Date)} ; {Include Date : Sum(Headcount)} gives us the right results but again there is performance lag. We would need a formula like

      {FIXED MAX(Date) : Sum(Headcount)} which does not work as aggregated values cannot be defined in the place of a dimension.

       

      Empl IDDate
      Headcount
      101/01/20181
      201/01/20181
      301/01/20181
      401/01/20181
      501/01/20181
      102/01/20181
      202/01/20181
      302/01/20181
      402/01/20181
      502/01/20181
      103/01/20181
      303/01/20181
      403/01/20181
      503/01/20181
      603/01/20181
      703/01/20181
      803/01/20181
      903/01/20181
      1003/01/20181

       

      Regards,

      Anupam Singh