topic Re: Display Top N% in QlikView

Display Top N%

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

Hi all,

I'm presenting sample data here and I need to display top 5 %'s

Sample Data

Load * Inline

[
Customer, Sales
A, 100
B, 120
C, 300
D, 400
E, 75
F, 50
G, 125
H, 2000
I, 250
];

Added calculated dimension as

=IF(Aggr(Rank(SUM(Sales),4),Customer)<=5,Customer)

expression as

SUM({<Customer = {"=Rank(SUM(Sales),4)<=5"}>}Sales)

here I need to avoid highlighted row. Adding sample app here. Pls. advise.

Br, Balanandam

Re: Display Top N%

sunny_talwar — Thu, 11 Jan 2018 16:26:34 GMT

Check 'Suppress When Value Is Null' on the dimension tab of chart properties

Re: Display Top N%

Anil_Babu_Samineni — Thu, 11 Jan 2018 16:27:43 GMT

Try to disable enable the Suppress Null value in dimension level

Re: Display Top N%

sunny_talwar — Thu, 11 Jan 2018 16:29:33 GMT

Do you mean enable?

Re: Display Top N%

balanandam — Thu, 11 Jan 2018 16:37:53 GMT

Anil & Sunny,

I tried both disable and enable. But if you see sales% column, H sales is 2000 and the actual share 58.48% (if not suppressed NULL on Dimension) and share will be 65.04% for the same when suppressed NULL on Dimension. This is the problem exactly.

Br, Balanandam

Re: Display Top N%

sunny_talwar — Thu, 11 Jan 2018 16:49:07 GMT

Try this

Dimension

Customer

Expressions

Sum({<Customer = {"=Rank(SUM(Sales),4)<=5"}>}Sales)

Sum({<Customer = {"=Rank(SUM(Sales),4) <= 5"}>}Sales)/vTotalSales

Re: Display Top N%

paulferguson80 — Thu, 11 Jan 2018 16:53:08 GMT

Hi,

I may be over simplifying the question but the null is caused by the calculation.

Would it not work the way you want it to with:

Dimension - Customer

Sales# - Sum(Sales)

Sales% - Sum(Sales) / Sum(TOTAL Sales)

Then limiting the dimension using "Show values that are" - Greater Than 5% relative to the total

and then un tick "Show Others"

I think this would give you what you want to display?

Thanks Paul

Re: Display Top N%

balanandam — Thu, 11 Jan 2018 16:58:31 GMT

Thanks Sunny, this is so helpful. And, one more thing here is

when I click on Dimension sales% getting changed i.e 100.00%. Can we make constant i.e 8.77% ?

Sorry If my ask is not reasonable or funny ?

Br, Balanandam

Re: Display Top N%

sunny_talwar — Thu, 11 Jan 2018 17:01:21 GMT

Update your variable to this

=Sum({<Customer>}Sales)

Re: Display Top N%

balanandam — Thu, 11 Jan 2018 17:06:56 GMT

Thank you so much!

Re: Display Top N%

balanandam — Thu, 11 Jan 2018 17:09:54 GMT

Hi Paul,

thanks for response. this is helpful in my other cases.

Br, Balanandam