Top performer KPI (rank aggregate) help

[#1]

Hi I'm struggling with an expression to identify a Top performer as a KPI measure in Qlik Sense.

I'm using the current statement to identify the total count of NPS surveys:

Count({$<Period_seq={$(=max(Period_seq))},Department = {'CE'}>} NPS)

I want to identify which Agent has the highest score - with the result being the name of the Agent.

I've tried various rank and aggregate commands in other discussion threads that have worked for others but I can't seem to be able to get it to work in this scenario.

thanks

[sdmech81]

May be this:

Ranks(Count({$<Period_seq={$(=max(Period_seq))},Department = {'CE'}>} NPS),10)

Plss try n let me know..Also try to put ur above expression in variable and then use Rank function like

=Rank(variable,10)

Sachin

[#3]

Thanks for trying.  This didn't work or I didn't understand.

I think I will need to use an aggregate function to get the top ranked performance.

The data is in rows for each NPS score,  which includes a flag with agent name in each now.  I want to include different KPI measures that return names, one for highest volume, one for highest score etc.

the expression:

Count({$<Period_seq={$(=max(Period_seq))},Department = {'CE'}>} NPS)

correctly counts the number of scores.  I now need to change this to count the number of scores by agent and to rank the highest for the KPI measure.

the variable name I need to aggregate and rank by is called Agent.

Sorry I'm not that familiar with rank and aggregate commands so I'm struggling with this one.

[sdmech81]

May be this:

Rank(Aggr(Count({$<Period_seq={$(=max(Period_seq))},Department = {'CE'}>} NPS),agent),10)

Plss try..

[#5]

Sorry no that doesn't work.  

What is the 10 for?  I just want the highest value.

thanks for trying

[#6]

min(aggr(rank(Count({$<Period_seq={$(=max(Period_seq))},Department = {'CE'}>} NPS)),Agent))

This seems to work in the fact that it is delivering the no 1 rank to the KPI (and if I put this expression in a table  with [Agent] it correctly puts a 1 next to the top performer).  Now I just need the KPI value to say the name of the top performer and not the number of the rank.

Can you help?

Thank you!

[sdmech81]

This one:

Max(Aggr(Count({$<Period_seq={$(=max(Period_seq))},Department = {'CE'}>} NPS),agent))

U want count of NPS and the aggregate them on Agent upon that uwant max value ryt?

Sachin

[sdmech81]

Oh ok,,

Sure will do tht.Just few minutes literally not infront of system..:)

Will check it..

Sachin

[sdmech81]

Hi,

Can you group by the data inside script and create table in data model.

A table which will contain the data where in the data is already grouped on the basis of agent??

So that you write set expressions directly..

OR u plss send me some sample data I will do it and give u QVW file by eod.

sachin

[#10]

HI,

Please see the screen shot from when I use the formula:

min(aggr(rank(Count({$<Period_seq={$(=max(Period_seq))},Department = {'CE'}>} NPS)),Agent))

I would like the 1 next to the star in the KPI to be the name of the Agent ranked as 1.

I have attached a sample data extract for your reference

QlikID	Period_seq	Agent	Department	NPS
1101	13	CCC	CE	-1
1102	13	GGG	CE	7
1103	13	BBB	CE	10
1104	13	HHH	CE	8
1105	13	CCC	CE	9
1106	13	CCC	CE	-1
1107	13	AAA	CE	-1
1108	13	HHH	CE	9
1109	13	CCC	CE	10
1110	13	CCC	CE	-1
1111	13	GGG	CE	9
1112	13	CCC	CE	-1
1113	13	CCC	CE	-1
1114	13	AAA	CE	10
1115	13	CCC	CE	-1
1116	13	HHH	CE	-1
1117	13	GGG	CE	10
1118	13	CCC	CE	9
1119	13	HHH	CE	-1
1120	13	HHH	CE	9
1121	13	AAA	CE	-1
1122	13	CCC	CE	10
1123	13	HHH	CE	9
1124	13	GGG	CE	-1
1125	13	AAA	CE	10
1126	13	HHH	CE	9
1127	13	AAA	CE	5
1128	13	HHH	CE	9
1129	13	CCC	CE	-1
1130	13	AAA	CE	7
1131	13	CCC	CE	-1
1132	13	Unknown	CE	-1
1133	13	Unknown	CE	-1
1134	13	Unknown	CE	-1
1135	13	Unknown	CE	-1