topic Re: Display average TOP 5 for all rows in QlikView

Display average TOP 5 for all rows

dafnis14 — Sat, 27 Aug 2016 15:57:13 GMT

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!

Re: Display average TOP 5 for all rows

kaushiknsolanki — Sat, 27 Aug 2016 19:35:52 GMT

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

sunny_talwar — Sat, 27 Aug 2016 22:48:06 GMT

Are you looking to get this?

May be this:

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

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

Re: Display average TOP 5 for all rows

dafnis14 — Sun, 28 Aug 2016 03:42:51 GMT

Awesome Kaushik!

Thank you very much for your help!

Re: Display average TOP 5 for all rows

dafnis14 — Sun, 28 Aug 2016 05:54:15 GMT

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 🙂