Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
dafnis14
Contributor III

Display average TOP 5 for all rows

Hi,

I have a table where I use an expression taken from another post

Ranking max top 5

However, I need to show the average of the TOP 5 for any branch, even if the branch

is not in the top 5. Is it possible?

Attached is a very simplified model of my requirement, ere only the top 5 branched are displayed.

Thanks!

1 Solution

Accepted Solutions

Re: Display average TOP 5 for all rows

Try this expression.

avg({<Branch = {"=Rank(TOTAL avg(Score),0)<=5"}>}Total<Year> Score)

Hope this is what you want.

Regards,

Kaushik Solanki

4 Replies

Re: Display average TOP 5 for all rows

Try this expression.

avg({<Branch = {"=Rank(TOTAL avg(Score),0)<=5"}>}Total<Year> Score)

Hope this is what you want.

Regards,

Kaushik Solanki

Re: Display average TOP 5 for all rows

Are you looking to get this?

Capture.PNG

May be this:

Avg(TOTAL <Year> {<Branch = {"=Rank(TOTAL Avg(Score), 0) <= 5"}>} Score) + Avg({1} 0)

or

Avg(TOTAL {<Branch = {"=Rank(TOTAL Avg(Score), 0) <= 5"}>} Score) + Avg({1} 0)

dafnis14
Contributor III

Re: Display average TOP 5 for all rows

Awesome Kaushik!

Thank you very much for your help!

dafnis14
Contributor III

Re: Display average TOP 5 for all rows

Hi Sunny,

I saw your solution in another post, and I admit it was too sophisticated for me to understand. (Avg{1} 0).. even with your explanations.

But I will check it more seriously.

The sample requirement might not make sense, but in reality, I would need to show one row per  sub-category, and each sub-category is linked to a different upper category.  

Thanks for your kind help 🙂