3 Replies Latest reply: Feb 28, 2017 2:32 PM by Richard Judkins RSS

    Sum(if... to return null

    Richard Judkins

      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!