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: 
Anonymous
Not applicable

Sum(if... to return null

Hey all

Using Qlik Sense Server.

So I have this fx, Sum(if(year(TRANS_DATE)=2017,QTY,0)), where TRANS_DATE is the date of a sales transaction, and QTY is basically the quantity of sales.

Currently, in my bar graph, it is returning 0 for the future months, which is right as there haven't, of course, been any sales made in the future.

However, this bar graph is to show the whole year's targets and sales (month by month) - I want the Sales bars for future months to be blank, not show 0.

The way to do this is, of course, untick 'show null values' in the Dimension. However, for this to work I need that script to return null if the QTY is 0, not '0' as it is now.

And I can't just change the 0 to null, as the Sum is in fact 0, not an invalid return or anything.

But, unsure how to do that - I imagine there's a few imbedded if statements needed in there, but I don't have the knowledge yet on how to do that - any advice greatly appreciate thanks!

3 Replies
sunny_talwar

Have you tried to uncheck 'Show Zero Values' under Add-ons/Data Handling?

Capture.PNG

sumitjadhav
Creator II
Creator II

Hi Richard,

Can you send me the dummy data for more clarifications,so that we can Find Some Solution.

Thanks & Regards,

Sumit Jadhav.

Anonymous
Not applicable
Author

Thanks Sunny, but yes, that's been unchecked, and it doesn't do anything.