Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
Showing results for 
Search instead for 
Did you mean: 

Filter Aggr Results by Maximum Dimension Value!

Is it possible to filter the results of an aggr function that is returning multiple values for one dimension by taking the maximum value of another dimension included in the aggr array?

I have an aggr function expression in a pivot table that looks similar to this:

RangeSum( before( aggr( count(distinct if(<date criteria>, EmployeeName)), Title, EmployeeName, (Year,(NUMERIC, ASCENDING))) ) )

where Year is the dimension I'm using for inter-record prior-year results.

My problem is that because some employees are promoted mid-year, their Title changes, resulting in two records  in the data model associated with a given Year and EmployeeName, and the aggr expression above returns those two records when I only want one -- the record with the more senior Title.

If I remove Title from the aggr function "group by" dimensions, I get one result, but it's the earlier-in-time, lower title, which means I'm incrementing the wrong Title count.

In the load script, Title is assigned a numeric value reflecting the org chart hierarchy.

Can the results of aggr be "filtered", where for a given Year and EmployeeName, only the record with the more senior Title is included?

In SQL it would look something like this:

SELECT Year, EmployeeName, Max(Title)

Labels (1)
1 Reply

Hi ShepherdKai

Have you found the solution for that need? I hope so.

Qlikview is a powerful tool for solving puzzles like this.

Personally, I recommend this article for understanding advanced use cases for the aggr function: https://www.analyticsvidhya.com/blog/2014/02/aggr/#:~:text=AGGR

Best regards,