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!
