Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to skip zero values

Hi everyone,

I have created measure which calculates customers debts and I have a lot of customers with zero debts.

I use function Sum(Debet-Credit). An example shown below.

I receive values, and I dont want to see last 3 customers (because I have a lot of records with zero values)

Customers    Debt

Customer1    1000

Customer2    500

Customer3    450

Customer4    300

Customer5    0

Customer6    0

Customer7    0

Does anybody knows how to do it in Qlik Sense Desktop, I mean is there any function which skips zero values?

Thank in advance for your help.

Best regards

Vidas

17 Replies
JonnyPoole
Employee
Employee

From what i can gather the following documentation snippet is at play here..   a sum() on a field that has true nulls will 'not normally be affected by NULLs' and 'dont use the record with the NULL in the calculation'.  That *i think* means you are going to get a 0.

Capture2.PNG

So if i load data that has an explicit NULL (see below for a manufactured NULL in the load script)

Capture.PNG

The sum() shows 0 for the null record, but as a dimension it shows null() and the nullcount() function picks it up.

Capture2.PNG

hic

Not applicable
Author

So it seems like the only way to suppress these manufactured 0 values in a table is to use a set expression? This is poor work around because it makes using a master expression not possible. The expression will need to reference the dimensions in the object.


To me the way null is being handled/displayed in an aggregation function is incorrect. The Sum of a null is not 0. Do you agree or am I missing something?


thanks for the help

JonnyPoole
Employee
Employee

The other way is use a calculated dimension that checks for Nulls in the measure expression. If its is a null , then assign null() and turn off the checkbox 'show nulls' for that same dimension.

So in the previous example the first row can be supressed by changing F1 to be the following expression:

if( aggr(nullcount(Amount)>0,F1),null(),F1)


and turning off the 'show nulls' checkbox.


As for the right / wrongness in how sum() works on NULLs that is worth posing as a followup to this blog entry. ( i also tagged HIC in the previous post in case he can comment directly).


NULL handling in QlikView


Capture.PNG

Capture2.PNG

Not applicable
Author

Thanks again Jonathan,

I guess it is bugging me so much because QlikView suppresses the zeros created by default. Where in Sense there is no option to easily do this. The results of sum(null()) are the same in both products, IMO QlikView handles this more gracefully. Suppress zeros is an option in the bar chart. I guess it just didn't make the cut for the table objects.

3-13-2015 10-50-52 AM.png

The work-arounds do work but also introduce a whole bunch of complexity and challenges as well. In the case you show above, a dimension would need to be created for each instance of a measure. The combination of these things can total in the hundreds making things very confusing.

JonnyPoole
Employee
Employee

I don't disagree with you.  the suppress zeros option on the presentation tab of a chart object in QV is there for all charts and summarized tabular displays are INCLUDED in that... straight tables / pivot tables.

In Sense, charts DO have a supress zero option in the addons section  (as patrick mentioned below) but this does not exist in a table object, even if the table object is very similar to a straight table in QV, does aggregations etc... zero suppression should be in there.

I don't think this is the first community post i've seen on this . i'll pass it around internally.

adambrian
Contributor III
Contributor III

Hi micheal tarallo,

from your file qvf, can i hide customer 3? i want all customer but not customer 3?

Anonymous
Not applicable
Author

Hi Patrik,

Perfect, you solved my issue.

Thank you,

Ozzie

rajeshqvd
Creator II
Creator II

Go to >> Add-ons>>Uncheck Include Zero Values