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

[#1]

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!

[Oleg_Troyansky]

How about replacing the nested AGGR with a simple aggregation and the condition that selects the last available date, something like this:

sum({<Date={$(=max(Date))}>}   Value)

I might be overly simplifying your needs, but this is a direction that I'd explore...

cheers,

Oleg Troyansky

Come and learn Set Analysis and AGGR with me at the Masters Summit for QlikView.