Sum of Top 5 displayed in a KPI

[samnoor_27]

Hi,

I have a Table Client which contains the Client and a Ticket Id assigned to the client. (Refer to Table 1)

First step: I wish to count the number of tickets for each client (Refer to Table 2).

2nd step: Find the top 5 clients, then sum the number of tickets and eventually display it in a KPI. (I should get 10 as total number of tickets for the top 5).

I have the tried something like this in my set analysis, but I'm afraid it's not good :

Sum({<Client = {'=Rank(Count(Id)) <=5'}>} Id)

I'm not getting the sum 10 for the top 5.

Grateful if you could advise on how to build this set analysis correctly.

Thanks for your help!

[Channa]

Count(Distinct {<Client={"=RANK(COUNT(ID))>5"}>} Client)

try this it will bring top 5 clients 

[agigliotti · Accepted solution]

maybe this:
Count( {< Client = {"=Rank(Count([Ticket id]))<=5"} >} [Ticket id] )

[Channa]

=If(Aggr(Rank(Count(ID)),Client)<=5,Client)

it will bring top5 clients

[samnoor_27]

Hi,

Thank you all for your response.

 @agigliotti  I have tried your suggestion and it happens to work! Thanks for this.

In the same instance, could you kindly advise if I could apply a date filter in this set. For example, I wish to get the top five for last month of this year. Is this set ok? I'm not sure I'm getting the correct answer..

Count({<Client= {'=Rank(Count({1<[Created at.autoCalendar.MonthsAgo]={1}, [Created at.autoCalendar.YearsAgo]={0}>} Id)) <=5'}>}Id)

Thanks for your help

[agigliotti]

maybe this one:
Count( {< Client = {"=Rank(Count( {< [Created at.autoCalendar.MonthsAgo]={'1'},
[Created at.autoCalendar.YearsAgo] = {'0'} >} [Ticket id]))<=5"} >} [Ticket id] )