Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

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

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)

...

GROUP BY Year, EmployeeName

Thanks!
1 Reply
Marcos_Ferreira_dos_Santos

Hi OTarna,

As far as I know, there is no way to "filter" the results of aggr live on the panel. 

What if you load only the last Title for each Employee, solving your issue during script/load time ?

Good luck,

Marcos