Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld Online 2021, May 10-12: Our Free, Virtual, Global Event REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
biggusdoggus
Creator
Creator

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.

biggusdoggus
Creator
Creator
Author

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