topic Re: ranking top 10 in QlikView

ranking top 10

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

Hi Experts,

I want to show top 10 products based on its percent. Please help.

Re: ranking top 10

Nagaraju_KCS — Wed, 25 Apr 2018 09:36:54 GMT

May be this...

if(aggr(rank(sum(Margin)/sum(Sales)),Product)<=10,sum(Margin)/sum(Sales))

Re: ranking top 10

bc-thebruuu — Wed, 25 Apr 2018 09:39:59 GMT

What is your expected result?

As far as I see,

Product should be sorted alphabetically and Region wrt to the expression you used for product

Re: ranking top 10

surajap123 — Wed, 25 Apr 2018 09:45:03 GMT

Hi Raju,

Thanks for the expression.

Sorry, I have slight change in the requirement. I actually want top 10 worst products and the product dimension should be sorted alphabetically.

Thanks!

Re: ranking top 10

Anonymous — Wed, 25 Apr 2018 09:56:34 GMT

your expected o/p may be like below

Re: ranking top 10

YoussefBelloum — Wed, 25 Apr 2018 09:57:11 GMT

take a look at this:

Re: ranking top 10

Nagaraju_KCS — Wed, 25 Apr 2018 10:17:18 GMT

PFA,

Re: ranking top 10

surajap123 — Wed, 25 Apr 2018 11:01:27 GMT

Thanks Youssef & Raju,

I tried to implement it in my main application. But our application has many expressions in the pivot table which is causing issue.

I am wondering if i can use the same expression to create a calculated dimension, so i can have the correct worst 10 products always.

=if(rank(-(sum(Margin)/sum(Sales)))<=10, sum(Margin)/sum(Sales))

Could you help me with the expression.

Re: ranking top 10

YoussefBelloum — Wed, 25 Apr 2018 11:33:51 GMT

Hi,

to be able to show the worst 10 dimension values correctly on your pivot table when you have multiple measure, you should add the Ranking condition on EVERY measure

Re: ranking top 10

surajap123 — Wed, 25 Apr 2018 11:48:46 GMT

Hi Youssef,

Infact i have only one measure. All the other measures are background colors and trends based on the same main measure.

So, instead of modifying all the expressions in the chart. I think, its eazy to use calculated dimension.

Please help with the expression.

Thanks!

Re: ranking top 10

YoussefBelloum — Wed, 25 Apr 2018 11:59:15 GMT

like what you can see above, on the example you provided, it is working, now if it is a bit different, I have to test it again..

would you be able to scramble the data if it is sensitive and attach your app here ? or just attach another sample app which represent the final pivot structure

Re: ranking top 10

surajap123 — Wed, 25 Apr 2018 12:34:37 GMT

Hi Youssef,

Here is the attachment. May be you can show worst 5 products, as the data is less.

Re: ranking top 10

YoussefBelloum — Wed, 25 Apr 2018 12:41:39 GMT

here it is:

Re: ranking top 10

surajap123 — Wed, 25 Apr 2018 13:01:20 GMT

Thanks for all your help!

Re: ranking top 10

YoussefBelloum — Wed, 25 Apr 2018 13:10:27 GMT

You're welcome