Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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.