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
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
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
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
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 |
It must work now:
=FirstSortedValue(Agent,-aggr(Count({<Department={'CE'},Period_seq={'=14'}>}NPS),Agent))
Sachin
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
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!
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.
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?
fixed it myself!! thanks Allllllll!