Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
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

21 Replies
sdmech81
Specialist
Specialist

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

Not applicable
Author

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 achieveCapture2.JPGCapture3.JPG




ahaahaaha
Master
Master

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

Not applicable
Author

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:

QlikIDPeriod_seqAgentDepartmentNPS
110113CCCCE-1
110213GGGCE7
110313BBBYYYYYYY10
110413HHHYYYYYYY8
110513CCCYYYYYYY9
110613CCCCE-1
110713AAACE-1
110814HHHCE9
110914CCCCE10
111014CCCCE-1
111114GGGCE9
111214CCCCE-1
111314CCCCE-1
111414AAACE10
111514CCCCE-1
111614HHHCE-1
111714GGGCE10
111814CCCCE9
111914HHHCE-1
112014HHHCE9
112114AAACE-1
112214CCCYYYYYYY10
112314HHHYYYYYYY9
112414GGGYYYYYYY-1
112514AAACE10
112614HHHCE9
112714AAACE5
112814HHHCE9
112914CCCCE-1
113014AAACE7
sdmech81
Specialist
Specialist

It must work now: 

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

Sachin

View solution in original post

sdmech81
Specialist
Specialist

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

Not applicable
Author

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
Specialist
Specialist

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.

Not applicable
Author

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?

Not applicable
Author

fixed it myself!! thanks Allllllll!