Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

biggusdoggus
Contributor

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

Re: Sum(if... to return null

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

Capture.PNG

sumitjadhav
Contributor II

Re: Sum(if... to return null

Hi Richard,

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

Thanks & Regards,

Sumit Jadhav.

biggusdoggus
Contributor

Re: Sum(if... to return null

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