topic Re: Variables in set analysis in App Development

Variables in set analysis

danaleota1 — Tue, 29 Jun 2021 16:43:59 GMT

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.

Re: Variables in set analysis

edwin — Tue, 29 Jun 2021 18:04:28 GMT

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)

Re: Variables in set analysis

edwin — Tue, 29 Jun 2021 18:04:52 GMT

the variable needs to be immediate

Re: Variables in set analysis

edwin — Tue, 29 Jun 2021 18:17:06 GMT

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

Re: Variables in set analysis

edwin — Tue, 29 Jun 2021 18:28:58 GMT

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.

Re: Variables in set analysis

danaleota1 — Tue, 29 Jun 2021 19:11:53 GMT

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!