Set analysis is not working with aggr

[Daya_Qlik]

Hi,
We have a scenario to show latest job description for an employee based on the max change date.
I have tried using aggr() with set analysis.

"Max(Aggr(Max({<$(vIgnoreTimeSel), [flag for area alighment]={1}, _Flag_Job_Alignment={1}, _Flag_Restaurant={1}, _Flag_Termed={0}>}
[Change Date])
,[Employee Full Name], [Job Desc]))"

Table: 

Employee Full Name	Job Desc	Change Date
ABC	Shift Manager	3/16/2020
ABC	Shift Manager In-Training	2/10/2020
ABC	Team Trainer	9/23/2019

Expected Output:

Employee Full Name	Job Desc	Change Date
ABC	Shift Manager	3/16/2020

Thanks.

[tresesco · Accepted solution]

I guess if you could use firstsortedvalue() instead, it would be easier. Try like:

FirstSortedValue(<Set analysis> [Job Description], -[Change Date])

[agigliotti]

Hello @Daya_Qlik ,

You could try with:

=Aggr( Max( {< $(vIgnoreTimeSel), [flag for area alighment] = {1}, _Flag_Job_Alignment = {1}, _Flag_Restaurant = {1}, _Flag_Termed = {0} >} [Change Date] ), [Employee Full Name] )  for "Change Date" dimension 

and

=Aggr( if( [Change Date] = Max( {< $(vIgnoreTimeSel), [flag for area alighment] = {1}, _Flag_Job_Alignment = {1}, _Flag_Restaurant = {1}, _Flag_Termed = {0} >} [Change Date] ), [Job Desc] ), [Employee Full Name] )  for "Job Desc " dimension 

I hope it can help.

Best Regards

[AnalyticsBoost]

=if(min(total aggr(rank(total Num(Date#([Change Date], 'MM/DD/YYYY'))),[Job Desc]))=rank(total Num(Date#([Change Date], 'MM/DD/YYYY'))) ,[Job Desc])

[Daya_Qlik]

Hi Agigliotti,

Thank you for your solution.

We applied the given formula, the job description is showing blank.

Can you please check and thanks in advance.

[agigliotti]

give a try using  [Job Desc] as dimension...

[Daya_Qlik]

we have taken [job desc] as a dimension and [change date] as measure.

[Daya_Qlik]

Hi Tresesco,

Thanks for the solution. It worked.

Thanks.

[agigliotti]

I said above to use both as dimensions even [change date].