Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for
Did you mean:
Creator

Qlik Sense suppress zero/null values in Measure

Hi all,

I have done a lot of searches but I am unable to find a best solution

I have  a Qlik sense application, I am using a table, in the Dimension i have Product and in measure i have sum(sales) - sum(margin)

I get the result but I also get zero or null values, how can I suppress the zero/null values?

I tried using the Add-ons -> Data handling and untick Include zero values but the zero values are still showing

I have tried if(sum(sales) - sum(margin) > 0, sum(sales) - sum(margin), 0)    and i have tried if(sum(sales) - sum(margin) > 0, sum(sales) - sum(margin))

1 Solution

Accepted Solutions
Partner - Champion

that's why you are still getting those rows because you have 2 measures not equals to zero.

to get it works you should use an if condition to force 0 value for those measures.

12 Replies
MVP

Do you think that it might be a real small non-zero number? Like 0.000001?

Creator
Author

Hello Sunny,

I have checked that and I am sure it is a zero number and to make sure I have checked the sales and margin for this product and they are both showing ex; 100k

Partner - Champion

are you sure all measures value is 0 and not ex. 0.001 or NULL ?

Unticking include zero values under add-on ignores all table rows with 0 value NOT NULL in all measures.

Partner - Creator II

Do you have more than one measure in the table?  Suppress zero will work if all the measures are zero.

MVP

Again if you are not looking at decimals or looking at too less decimals, you won't know. For example, Sum(Sales) = 96.23000000000001 and Sum(Margin) = 96.23

Both will look 96.23 if you have two decimals, but in fact they are not... may be try this

Round(Sum(Sales)) - Round(Sum(Margin))

Creator
Author

Hi Shivanand,

Yes, i have 2 more measures that are not null/zero values

but I want to explode them based on the sum above

Creator
Author

I have done the round and the zero values are still showing

Creator III

try this

Sum({<Product = {"=Sum(sales)-Sum(margin)<>0"}>}sales-margin)

Partner - Champion

that's why you are still getting those rows because you have 2 measures not equals to zero.

to get it works you should use an if condition to force 0 value for those measures.