Announcements
The way to achieve your own success is the willingness to help somebody else. Go for it!
cancel
Showing results for
Did you mean:
Contributor II

## Set analysis is not working with aggr

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.

1 Solution

Accepted Solutions
MVP

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

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

8 Replies
MVP

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

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

Partner - Champion

Hello @DayaWendys ,

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

Contributor III

=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])

Contributor II
Author

Hi Agigliotti,

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

Partner - Champion

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

Contributor II
Author

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

Contributor II
Author

Hi Tresesco,

Thanks for the solution. It worked.

Thanks.

Partner - Champion

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

Tags
Community Browser