Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
motzfeldt01
Partner - Contributor II
Partner - Contributor II

KPI: Average of top 10 performer

Hi,

I'm currently doing a benchmark analysis and I have to find the average GrossResult of the top 5 performers.

For example:

CompanyIDGrossResult
136,54
2302,51
329,71
4103,02
535,1
6101,63
732,76
8352,64
924,34
10228,89

Here I'm interested in finding the number 217,738.

Does anybody have an idea how to do this in a KPI box?

8 Replies
prashantsanchet
Creator
Creator

Hi,

Please try to use below expression:

avg({<CompanyID = {'=Rank(Sum(GrossResult)) <=5'}>}GrossResult)

Hope this will help,

Regards,

Prashant

motzfeldt01
Partner - Contributor II
Partner - Contributor II
Author

Hi,

It worked, thanks!

But what if I have a variable called vChosenIndustry, to identify a filtered industry. Where do I have to put that statement. Thus I don't get the overall top 5, but instead the top 5 of the filtered industry?

The detailed version:

I have the following code to calculate the average GrossResult in a particular industry

avg({<[CompanyID]=, [CompanyName]=, Industry={$(vChosenIndustry)}>}[GrossResult])


But as you know, now I only want the average for top 5.

Kind regards

Andreas

shraddha_g
Partner - Master III
Partner - Master III

Try

avg({<CompanyID = {'=Rank(Sum({<IndustryField = {"$(=vChosenIndustry)"}>}GrossResult)) <=5'}>}GrossResult)

motzfeldt01
Partner - Contributor II
Partner - Contributor II
Author

It gives me 193,7 but should give me 217,7.

193,7 was also the number it gave me before, which doesn't make sense after we added "{<IndustryField = {"$(=vChosenIndustry)"}>}" .

shraddha_g
Partner - Master III
Partner - Master III

Please check what value the variable is giving

$(=vChosenIndustry)


or Simply vChosenIndustry

motzfeldt01
Partner - Contributor II
Partner - Contributor II
Author

Oh, I forgot. It's because I have to specify from what reporting year we have to use the Grossresult. Otherwise it will sum all the years. So, I tried to add that perspective to your code: avg({} [GrossResult]) But it doesn't show any value. Can you spot the what I'm doing wrong?

shraddha_g
Partner - Master III
Partner - Master III

can you please show what you have done

motzfeldt01
Partner - Contributor II
Partner - Contributor II
Author

Sorry, here is the code:

avg({<[CompanyID] =

{'=Rank(Sum({<Industry = {"$(=vChosenIndustry)"},

[XBRL Reporting year] = {"$(=vXBRLThisYear)"}>}[GrossResult])) <=5'}>}

[GrossResult])