Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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

Tags (2)
16 Replies
ecolomer
Honored Contributor II

Re: How to skip zero values

in the dimension, put this formula:

=if(Sum(Debet-Credit)<>0, Customer, Null())

and clik option to no show null values

Re: How to skip zero values

Another way also in the expression part write

If( Sum(Debet-Credit) > 0 ,Sum(Debet-Credit))


Or


If( Sum( Total Debet-Credit) > 0 ,Sum(Debet-Credit))

This is for neglect the 0 values.

Regards

Anand

Highlighted
MVP
MVP

Re: How to skip zero values

Hi,

Supress zero values option in Presentation.

Regards,

Jagan.

Employee
Employee

Re: How to skip zero values

Hello Vidas -

Please note : unchecking this "Show null values" option - will suppress null values only - if these values are represented as 0, this option will not work, can you please verify if this works for you. It does not work for me using your sample data.

What does work is this:

Is select the dimension customers - and select the Limitation option and choose > 0

I attached a sample for you. Copy to C:\Users\<user profile>\Documents\Qlik\Sense\Apps and press F5 in the hub to see the app.

Let us know how you do

Please mark the appropriate replies as helpful / correct so our team and other members know that your question(s) has been answered to your satisfaction.

Regards,

Mike T

Qlik

Regards,
Mike Tarallo
Qlik
Not applicable

Re: How to skip zero values

Michael,

I am having the same issue but in my case I need to display both negative and positive values. Your solution excludes the negatives.

The bigger issue here is that when a Sum is done on a null value the result is displayed as zero, surporess null does not work.... Feature or Bug?

Employee
Employee

Re: How to skip zero values

You can handle that in a set analysis expression.

In mike's example , the following could replace  sum( Debt-Credit)

Sum( {<Customers={"=sum(Debt-Credit)>0 or sum(Debt-Credit)<0"}>} Debt-Credit)

This will only bring back customers where the sum was > or < 0.

Employee
Employee

Re: How to skip zero values

Hi David,

Have you checked in the property panel in the add-ons section. There is a show zero value option there in data handling.

Regards,

Patrik

Employee
Employee

Re: How to skip zero values

Also - just realized i didn't answer the 'bigger issue' question ... Can you post a sample QVF ? 

The reason for the ask  is that there is a lot of subtle differences between true Null values, missing values  (type 1 and type 2) , and empty values . 

http://community.qlik.com/blogs/qlikviewdesignblog/2013/04/30/null-the-invisible-nothing

http://community.qlik.com/docs/DOC-3155

A sum() on missing values will usually produce a 0 (see pf 10-11 of null or nothing whitepaper), but if there are true NULL values that have been calculated as such in Qlik or loaded from a database ,  then things can change.

Not applicable

Re: How to skip zero values

Thanks for your suggestions Jonathan. I tried inputting this in a few ways with similar results. You can check the qvf and xls. The data source we are actually using is Oracle. The values are loading as nulls. Unless I am still missing something it looks like sum of a null value is showing as zero.

Community Browser