compare against top 4 scores

[#1]

hi all,

I have a requirement where I need to compare company's score against the top 4  scores recorded . The data is as shown below:

company	score
A	10
B	4
C	4
D	5
E	6
G	8

I need a bar graph where first bar will show the score of company that is selected. Second bar will be the average of top 4 companies . no matter which company I choose, the second bar should always show average of top 4 companies.

I currently show average of all companies, how can I restrict it to show top 4 alone in second expression? No dimensions in this case.

First exp:  avg( {$} score)

second exp: avg({1} (score))

Thanks,

[Gysbert_Wassenaar · Accepted solution]

See attached qvw.

[#3]

Hi gwassenaar‌,

Thank you so much for the response. This is what I was looking for. However, in my actual scenario, I check for another flag before I average the score.

That is , avg({< flag={1}> }score) 

Can you tell me how I need to modify your second expression ? I am struggling with the syntax.

[Gysbert_Wassenaar]

Please post a qlikview document that explains your question.

[#5]

gwassenaar‌

Please see the attached example.

Here I check for two filters before deducing average. Profile and Flag.

[Gysbert_Wassenaar]

='top 4 average = ' & avg({1<Profile={'X'}, Flag={1},company={"=rank(sum({1<Profile={'X'}, Flag={1}>}score))<5"}>}score)

[#7]

Thanks for the solution, it works great!