1 Reply Latest reply: Feb 24, 2017 9:52 AM by Liam Hanninen RSS

    How to return null instead of 0 for false set analysis

    Liam Hanninen

      I have a set analysis that is restricting values based on a variable:

       

      Count({<[%Date] = {'>$(=(today()-(7*($(vTrailingWeeks)-1))-(7-weekday(today()))))'}>} [NewReports])

       

      where vTrailingWeeks is just a number 1-52.

       

      I'm trying to display trailing weeks. The above expression works! But when there are weeks with values of 0 they look just like the rest of the weeks that I intentionally excluded.

       

      Note the images below: The top chart shows all of the values. The part I outlined on the left are the ones deliberately excluded via the set analysis above. On the right there are 8 weeks allowed, note the four with zero value. My intention is to display all 8 weeks (see on the right) but leave out all of the rest (to the left).

       

      null.png

      But as you might expect if I uncheck 'Show Zero Values' in Data Handling in Add-ons all but the four with values disappear. And so I'm wondering if I can assign 'null' to all of the values on the left (top chart) so that I don't have to uncheck 'Show Zero Values' and instead uncheck 'Show Null Values'.

        • Re: How to return null instead of 0 for false set analysis
          Liam Hanninen

          Huzzah! I was able to do this by adding an if statement within the set analysis. Where it was equal to 0 (and True for set analysis) I gave it a value of .001. It therefore was not excluded when I unchecked 'Show Zero Values'. I finally changed the number formatting to Number and whole numbers so the .001 was not a distraction or point of confusion - it only appears as 0.

           

          (Sum({<[%Date] = {'>$(=(today()-(7*($(vTrailingWeeks)-1))-(7-weekday(today()))))'}>}

          if(

          aggr(sum([NewReports]),WeekEnding) = 0, .001,[NewReports])))

           

          null2.png