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]

Hi,

Plss get the attached QVW and the data in excel how that you gave.

Sorry fr delay was nt having system access.

Check the QVW script (I mean CTRL+E) then check hw data is aggregated thr.

Then thr is end table in front end tht I have added and also agent name in text box.

U can keep tht Sac2 table as data island without association.

Also if u expecting 'CCC' as ur answer plss use MAX()  in place of Min() in text box expression..

Hope this solves ur purpose..Thanks

Sachin

[#3]

Thanks for trying.

Is there no way to do this in the front end?  I have about 16 versions of 'Star' Agents I want to include on the dashboard.  I'd rather not have to do it as separate tables.  I feel like I'm so close with:

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

I just need to work out how to refer to the 1 as the agent rather than the rank.  I have tried your suggestion and I have the same answer.

Could a version of something like this work:

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

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

These don't work, but I think they show what I'm trying to achieve.

I've included some additional screen shots to help explain what I'm trying to achieve

[ahaahaaha]

Hi,

Maybe something like that (change the conditions, if I not understand correctly)

=firstsortedvalue({<Department = {'CE'}>}Agent, Aggr(Count(NPS), Agent))

In the attached file for an example of the implementation.

Regards,

Andrey

[#5]

Thanks Andry, that a returned an Agent name - thank you!...  but it was the wrong one.

I've attached a better sample data set below to explain what I mean.  With the data below the answer should be CCC

I need to use Max Period_seq (14) and Department (CE) and I'm looking for the highest count of records by agent within those conditions:

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

[sdmech81 · Accepted solution]

It must work now: 

=FirstSortedValue(Agent,-aggr(Count({<Department={'CE'},Period_seq={'=14'}>}NPS),Agent))

Sachin

[sdmech81]

In firstsorted value first u should pass what u want based on aggr function , in ur case its Agent.

Then u should pass aggr full function i.e ,-aggr(Count({<Department={'CE'},Period_seq={'=14'}>}NPS),Agent)  in ur case.

Do not say min max /minbcs it will sort the value in ascending order by default

So in ur case u need to say specifically - (minus)  so tht first sorted value sorts it in descending order.(see in above soln I have put -(minus) bfr aggr function)

Hope this solves (if u want minimum try removing -(minus)). Bye

Sachin

[#8]

Thanks Yes it works!!!

One more thing though.  the reason I was using Max Period_seq was so I didn't need to hard code when I load a new snapshot of data.

Anyway you can work that part into the above?

thank you thank you thank you!

[sdmech81]

You pass any variable in above expression It should take.

U need not hard code anything here..For example purpose I showed it like that.Declare variable fr tht max value and pass tht variable in place of 14..Hope done!

Sachin.

[#10]

thank you.

the below isn't working yet:

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

Is there something obviously wrong or does the max period statement just not work in combination with the above?

[#11]

fixed it myself!! thanks Allllllll!