Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Top performer KPI (rank aggregate) help

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

1 Solution

Accepted Solutions
sdmech81
Valued Contributor

Re: Top performer KPI (rank aggregate) help

It must work now: 

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

Sachin

21 Replies
sdmech81
Valued Contributor

Re: Top performer KPI (rank aggregate) help

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



Not applicable

Re: Top performer KPI (rank aggregate) help

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
Valued Contributor

Re: Top performer KPI (rank aggregate) help

May be this:

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


Plss try..

Not applicable

Re: Top performer KPI (rank aggregate) help

Sorry no that doesn't work.  

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

thanks for trying

Not applicable

Re: Top performer KPI (rank aggregate) help

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
Valued Contributor

Re: Top performer KPI (rank aggregate) help

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
Valued Contributor

Re: Top performer KPI (rank aggregate) help

Oh ok,,

Sure will do tht.Just few minutes literally not infront of system..Smiley Happy

Will check it..

Sachin

sdmech81
Valued Contributor

Re: Top performer KPI (rank aggregate) help

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

Not applicable

Re: Top performer KPI (rank aggregate) help

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

Capture1.JPG

 

QlikIDPeriod_seqAgentDepartmentNPS
110113CCCCE-1
110213GGGCE7
110313BBBCE10
110413HHHCE8
110513CCCCE9
110613CCCCE-1
110713AAACE-1
110813HHHCE9
110913CCCCE10
111013CCCCE-1
111113GGGCE9
111213CCCCE-1
111313CCCCE-1
111413AAACE10
111513CCCCE-1
111613HHHCE-1
111713GGGCE10
111813CCCCE9
111913HHHCE-1
112013HHHCE9
112113AAACE-1
112213CCCCE10
112313HHHCE9
112413GGGCE-1
112513AAACE10
112613HHHCE9
112713AAACE5
112813HHHCE9
112913CCCCE-1
113013AAACE7
113113CCCCE-1
113213UnknownCE-1
113313UnknownCE-1
113413UnknownCE-1
113513UnknownCE-1
Community Browser