Variables in set analysis

[danaleota1]

Hello, 

I have the following variable vTopSupervisor 

max(aggr(Count({<MonthYear = {'$(=$(vPreviousMonth))'}, txtCallType = {'External'}, txtFormType = {'Coaching'}, txtSegment = {'Member Services'} >} id), txtSupervisor))

This returns the count of ids for the supervisor with the max number of ids.  

I want to be able to return the value of the txtSupervisor field that this max count of ids by supervisor corresponds to.  

I was trying to create a second variable off of this first variable to do this.  

My goal is to use the second variable that returns the value of the txtSupervisor field in a set analysis expression in my chart.  

Any help or insight would be great.

[edwin · Accepted solution]

a quick solution would be:
create a variable (say vMaxSuperCount) to store the max count
in your table add calculated dimension, uncheck include Null values

=if(aggr(count(Super),Super)=$(vMaxSuperCount),Super)

[edwin]

the variable needs to be immediate

[edwin]

heres another example using a simplified data model (has a list of Super and we just count them

=count({<Super={"=count(Super)=max(total aggr(nodistinct count(Super),Super))"}>}Super)

maybe you can adopt this to your data model to something like :

count({<txtSupervisor={"=
Count({<MonthYear = {'$(=$(vPreviousMonth))'}, txtCallType = {'External'}, txtFormType = {'Coaching'}, txtSegment = {'Member Services'} >} id)
=
max(aggr(Count({<MonthYear = {'$(=$(vPreviousMonth))'}, txtCallType = {'External'}, txtFormType = {'Coaching'}, txtSegment = {'Member Services'} >} id), txtSupervisor))
"}
>} txtSupervisor)

hard to validate without the actual data model

[edwin]

also, i think there is a problem with your aggr expression, you need to add all the set analysis modifiers in your max() function, otherwise once selections are made especially with the field in your set analysis, the max will not give you the correct result.

[danaleota1]

for the dimension I followed along your first solution:

=if(aggr(Count({<MonthYear = {'$(=$(vPreviousMonth))'}, txtCallType = {'External'}, txtFormType = {'Coaching'}, txtSegment = {'Member Services'} >} id),txtSupervisor) = $(vTopSupervisor), txtSupervisor)

disabled nulls, 

and it returns the Supervisor's name that I was expecting.  I applied it to a bar chart where I wanted to only show the employees under that supervisor with the max count of ids (errors), I was able to do that using the employee as my first dimension then the if statement above for the supervisor for the second dimension.  it did work.  I will try to apply your other solutions as well, but the first response I applied right away and it worked. 

Thank you!