5 Replies Latest reply on Sep 8, 2016 1:15 PM by Paraskev Bentchev

# Aggregation of Null Values in Sheet Title shows as "All" instead of "Zero"

I have a sheet that shows a metric by weeks, and then a calculated field in the sheet title that shows me how many were yesterday. When there are none yesterday (thus passing in a NULL for that calculated field), it shows as "All Yesterday" in the title. I went ahead and made another calculated field that just says IF NULL then replace with 0. That works when it is null. However, when it is NOT null, then it reads as a range, "0 to 4 Yesterday". What am I doing wrong here? Why is it saying "All"?

• ###### 1. Re: Aggregation of Null Values in Sheet Title shows as "All" instead of "Zero"

Hi,

This All is something like * in visualizations. It means that there are more values than just one

I suggest you to put else clause in Yesterday Count

SUM(IF [Order Date] = TODAY()-1 then

[Number of Records]

else 0

end)

Hope this helps

• ###### 2. Re: Aggregation of Null Values in Sheet Title shows as "All" instead of "Zero"

Unfortunately adding that ELSE statement had the same effect as the IFNULL. They all show a range now.

"Abandons" is using IFNULL and the others are using the suggested change. I'm really at a loss here...it seems straightforward to replace the null with a zero.

• ###### 3. Re: Aggregation of Null Values in Sheet Title shows as "All" instead of "Zero"

next try

{SUM(IF [Order Date] = TODAY()-1 then

[Number of Records] else 0 end)}

this will be fixed LOD without any dimensions and will be 2 for every line

• ###### 4. Re: Aggregation of Null Values in Sheet Title shows as "All" instead of "Zero"

No luck. Now it shows as 43, which is the total number of records.

I went ahead and cleaned up the data and threw it in a packaged workbook.

"YesterdayBugfix" is what I've been putting the suggestions under

• ###### 5. Re: Aggregation of Null Values in Sheet Title shows as "All" instead of "Zero"

My goal is to have one value for all entries per worksheet, so as I see only event type is different and next try is to have again Fixed LOD:

{FIXED [event Type] : sum((IF [Dt Date] = TODAY()-1 then

[Number of Records] else 0 end))}

I'm not sure how will you interact within the dashboard, and if you have any filters consider including that dimensions in the LOD

So please find enclosed my proposal:)

If it is not OK, try to define calculated field that has same value for all entries per worksheet