1 Reply Latest reply: May 4, 2015 3:02 PM by Oleg Troyansky RSS

    Challenge: Find a better way to do my Aggr and FirstSortedValue to get the employee count on the most recent date.

      I need to always compile data based on the latest day in whatever time period is selected. For instance, I need a count of employees on any given day. If I'm looking at a month, I need the count on the last day of the month. If I'm looking at a quarter, I need the count on the last day of the quarter, etc.

       

      I use variations on the formula below in my visualization expressions and it works, but due to the 2 aggrs it is very resource heavy. JOIN_DT is what I use in my calendar and there is a JOIN_DT for every day. The second AGGR will sort the latest JOIN_DT to the top, based on a drill-down group of Organizations that I have as my Dimension.

       

      FirstSortedValue(

      Aggr(count(distinct EMPLOYEE_ID),JOIN_DT, $(=GetCurrentField(Organization_Drill))),

      Aggr(-JOIN_DT,JOIN_DT,$(=GetCurrentField(Organization_Drill))))

       

      You can see from the following that it gets more complex for different types of charts:

      FirstSortedValue(

      Aggr(count({$<PAY_PLAN = {'OK'}, CONFIRMED_FLG={'Y'}>}distinct EMPLOYEE_ID),JOIN_DT, $(=GetCurrentField(FYYr_MoYr_Drill)), $(=GetCurrentField(Organization_Drill))),

      Aggr(-JOIN_DT,JOIN_DT,$(=GetCurrentField(FYYr_MoYr_Drill)), $(=GetCurrentField(Organization_Drill))))

       

      Any ideas? Thank you so much in advance! I'm pretty new to Qlikview so all ideas are definitely welcomed!