Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

Best 3 Average

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.

Capture.PNG

1 Solution

Accepted Solutions
Highlighted
Master
Master

Re: Best 3 Average

12 Replies
Highlighted
Master III
Master III

Re: Best 3 Average

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

Highlighted
Creator III
Creator III

Re: Best 3 Average

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

Highlighted
Master
Master

Re: Best 3 Average

Please share data

Highlighted
Contributor
Contributor

Re: Best 3 Average

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

Highlighted
Contributor
Contributor

Re: Best 3 Average

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


]
;

Highlighted
Master
Master

Re: Best 3 Average

What's the logic for best 3 Averages?


Capture.JPG

Highlighted
Contributor
Contributor

Re: Best 3 Average

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.

Capture.PNG

Highlighted
Master
Master

Re: Best 3 Average

Highlighted
Contributor
Contributor

Re: Best 3 Average

Hi, how was this achieved in Qlik?  Cheers