Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
devarasu07
Master II
Master II

Exclude null values and calculate Sales %

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)

Expected output.jpg

6 Replies
marcus_sommer

Try it with hide NULL by your dimension.

- Marcus

sunny_talwar

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

Capture.PNG

devarasu07
Master II
Master II
Author

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

sunny_talwar

Try using set analysis instead of calculated dimension

Capture.PNG

sunny_talwar

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

Capture.PNG

antoniotiman
Master III
Master III

Maybe

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

Regards,

Antonio