How to return null instead of 0 for false set analysis

liam_hanninen — Thu, 23 Feb 2017 23:19:01 GMT

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).

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 — Fri, 24 Feb 2017 14:52:36 GMT

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])))

topic Re: How to return null instead of 0 for false set analysis in App Development

How to return null instead of 0 for false set analysis

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