Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
motzfeldt01
New 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?

Tags (2)
8 Replies
prashantsanchet
Contributor

Re: KPI: Average of top 10 performer

Hi,

Please try to use below expression:

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

Hope this will help,

Regards,

Prashant

motzfeldt01
New Contributor II

Re: KPI: Average of top 10 performer

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
Honored Contributor III

Re: KPI: Average of top 10 performer

Try

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

motzfeldt01
New Contributor II

Re: KPI: Average of top 10 performer

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
Honored Contributor III

Re: KPI: Average of top 10 performer

Please check what value the variable is giving

$(=vChosenIndustry)


or Simply vChosenIndustry

motzfeldt01
New Contributor II

Re: KPI: Average of top 10 performer

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
Honored Contributor III

Re: KPI: Average of top 10 performer

can you please show what you have done

motzfeldt01
New Contributor II

Re: KPI: Average of top 10 performer

Sorry, here is the code:

avg({<[CompanyID] =

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

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

[GrossResult])