topic Re: Top performer KPI (rank aggregate) help in QlikView

Top performer KPI (rank aggregate) help

Sat, 11 Feb 2017 00:47:18 GMT

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

Re: Top performer KPI (rank aggregate) help

sdmech81 — Sat, 11 Feb 2017 02:51:05 GMT

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

Re: Top performer KPI (rank aggregate) help

Sat, 11 Feb 2017 03:01:17 GMT

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.

Re: Top performer KPI (rank aggregate) help

sdmech81 — Sat, 11 Feb 2017 03:40:06 GMT

May be this:

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

Plss try..

Re: Top performer KPI (rank aggregate) help

Sat, 11 Feb 2017 03:47:25 GMT

Sorry no that doesn't work.

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

thanks for trying

Re: Top performer KPI (rank aggregate) help

Sat, 11 Feb 2017 04:35:55 GMT

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!

Re: Top performer KPI (rank aggregate) help

sdmech81 — Sat, 11 Feb 2017 04:39:35 GMT

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

Re: Top performer KPI (rank aggregate) help

sdmech81 — Sat, 11 Feb 2017 05:00:09 GMT

Oh ok,,

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

Will check it..

Sachin

Re: Top performer KPI (rank aggregate) help

sdmech81 — Sat, 11 Feb 2017 05:36:48 GMT

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

Re: Top performer KPI (rank aggregate) help

Sat, 11 Feb 2017 06:02:41 GMT

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

Re: Top performer KPI (rank aggregate) help

sdmech81 — Sat, 11 Feb 2017 08:10:25 GMT

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

Re: Top performer KPI (rank aggregate) help

Sat, 11 Feb 2017 08:39:49 GMT

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

Re: Top performer KPI (rank aggregate) help

ahaahaaha — Sat, 11 Feb 2017 09:06:04 GMT

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

Re: Top performer KPI (rank aggregate) help

Sat, 11 Feb 2017 09:23:18 GMT

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

Re: Top performer KPI (rank aggregate) help

sdmech81 — Sat, 11 Feb 2017 09:50:18 GMT

It must work now:

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

Sachin

Re: Top performer KPI (rank aggregate) help

sdmech81 — Sat, 11 Feb 2017 09:56:51 GMT

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

Re: Top performer KPI (rank aggregate) help

Sat, 11 Feb 2017 09:56:55 GMT

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!

Re: Top performer KPI (rank aggregate) help

sdmech81 — Sat, 11 Feb 2017 09:58:53 GMT

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.

Re: Top performer KPI (rank aggregate) help

Sat, 11 Feb 2017 10:03:27 GMT

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?

Re: Top performer KPI (rank aggregate) help

Sat, 11 Feb 2017 10:05:24 GMT

fixed it myself!! thanks Allllllll!