topic Re: Best 3 Average in QlikView

Best 3 Average

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I'm struggling to get a 'best 3 average' formula to calculate and display for all values. I was able to get the total average displaying for all but not the best 3 average. Image shows example data and the desired output. Any help would be greatly appreciated.

Re: Best 3 Average

Digvijay_Singh — Fri, 16 Mar 2018 18:03:31 GMT

Please share the excel data sample to try on this? Will save time to build test data.

Re: Best 3 Average

ychaitanya — Fri, 16 Mar 2018 18:43:16 GMT

Better if we do at Model Level than UI level. Where are you looking to put this logic

Re: Best 3 Average

shiveshsingh — Sat, 17 Mar 2018 02:50:58 GMT

Please share data

Re: Best 3 Average

Anonymous — Mon, 19 Mar 2018 08:44:37 GMT

I had thought in the load script but i'm new to qlikview so open to suggestions

Re: Best 3 Average

Anonymous — Mon, 19 Mar 2018 08:45:04 GMT

Sample data script:

LOAD * Inline [

Name, Year, Number
Company 1, 2014, 20
Company 1, 2014, 52
Company 1, 2015, 87
Company 1, 2014, 60
Company 1, 2014, 23
Company 1, 2015, 90
Company 2, 2014, 11
Company 2, 2014, 71
Company 2, 2015, 23
Company 2, 2014, 10
Company 2, 2015, 18
Company 2, 2014, 64
Company 3, 2014, 28
Company 3, 2014, 34
Company 3, 2015, 55
Company 3, 2014, 19
Company 3, 2015, 72
Company 4, 2014, 54
Company 4, 2014, 59
Company 4, 2015, 23
Company 4, 2015, 38
Company 5, 2014, 72
Company 5, 2014, 13
Company 5, 2015, 36
Company 5, 2015, 15

];

Re: Best 3 Average

shiveshsingh — Mon, 19 Mar 2018 16:57:07 GMT

What's the logic for best 3 Averages?

Re: Best 3 Average

Anonymous — Tue, 20 Mar 2018 10:38:36 GMT

where the rank is <=3, avg(number). In excel I use the AverageIfs function but I cant seem to get it to work in Qlik. Apologies the 1st pic is showing the wrong values.

I want it to display for all companies so that they can see how far away they are from the best 3 average - that's the tricky bit.

Re: Best 3 Average

shiveshsingh — Tue, 20 Mar 2018 16:43:58 GMT

PFA

Re: Best 3 Average

Anonymous — Thu, 22 Mar 2018 14:46:23 GMT

Hi, how was this achieved in Qlik? Cheers

Re: Best 3 Average

Anonymous — Thu, 22 Mar 2018 14:53:25 GMT

Hi ,

Use the below code in expression

=SUM(TOTAL<Year>(aggr((if(Rank(SUM(Number),4)<=3,sum(Number))),Year,Name)))/3

Thanks,

Venkata Sreekanth

Re: Best 3 Average

shiveshsingh — Thu, 22 Mar 2018 15:31:38 GMT

i have already attached the app for our reference.

any ways use this expression

sum(TOTAL<Year>(aggr((if(Rank(sum(Number),4)<=3,sum(Number))),Year,Name)))/3

Re: Best 3 Average

shiveshsingh — Thu, 22 Mar 2018 15:32:09 GMT

And yes, please close the thread now by marking correct/helpful answers