Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
It must work now:
=FirstSortedValue(Agent,-aggr(Count({<Department={'CE'},Period_seq={'=14'}>}NPS),Agent))
Sachin
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
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.
May be this:
Rank(Aggr(Count({$<Period_seq={$(=max(Period_seq))},Department = {'CE'}>} NPS),agent),10)
Plss try..
Sorry no that doesn't work.
What is the 10 for? I just want the highest value.
thanks for trying
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!
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
Oh ok,,
Sure will do tht.Just few minutes literally not infront of system..:)
Will check it..
Sachin
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
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 |