topic Re: Exclude null values and calculate Sales % in QlikView

Exclude null values and calculate Sales %

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

Hi All,

Please refer the attachment, I've used below expression and trying to achieve attached expected output screen (i.e exclude null from the country and calculate Sales %). please advise me. Thanks

Dimension:

Country

=if(WildMatch(Country,'SAUDI ARABIA','INDONESIA','UAE','THAILAND','MALAYSIA','VIETNAM','SOUTH KOREA','INDIA','PHILIPPINES','MACAU','CAMBODIA'),Country)

Measure:

Sales=Sum(TotalNetprice)

Sales %= Sum(TotalNetprice)/ Sum(TOTAL TotalNetprice)

Re: Exclude null values and calculate Sales %

marcus_sommer — Tue, 15 Nov 2016 17:19:22 GMT

Try it with hide NULL by your dimension.

- Marcus

Re: Exclude null values and calculate Sales %

sunny_talwar — Tue, 15 Nov 2016 17:28:36 GMT

Basically check the 'Suppress When Value is Null' on the dimension tab

Re: Exclude null values and calculate Sales %

devarasu07 — Tue, 15 Nov 2016 17:34:52 GMT

Hi Marcus,

Thanks for your reply. I have tried doing that method. But the Sales % still takes into account the NULL values. Any idea on how to apply where clause or any other logic in the sales % expression? [Attached the sample data and document]

Thanks

Re: Exclude null values and calculate Sales %

sunny_talwar — Tue, 15 Nov 2016 17:38:49 GMT

Try using set analysis instead of calculated dimension

Re: Exclude null values and calculate Sales %

sunny_talwar — Tue, 15 Nov 2016 17:41:26 GMT

Or if you have no issues in using a straight table instead of a pivot table, you can simplify your second expression by using 'relative' check mark for your second expression and use Column(1) as expression

Re: Exclude null values and calculate Sales %

antoniotiman — Tue, 15 Nov 2016 17:44:06 GMT

Maybe

sum(Sales)/RangeSum(Top(sum(Sales),1,NoOfRows()))

Regards,

Antonio