Displaying a 0 instead of a blank cell where there's no data for a given date
Hi. This is related to the How To article that Moshe Yossef kindly provided a while back - https://community.pyramidanalytics.com/t/p8hv3sr/how-to-create-a-dynamic-set-of-dates.
(I would have asked this question from the How To article itself but there was no text box to do so).
How do we display the empty cells in the final result to 0 (the final result is displayed at about 8:55 in Moshe's video); The empty cells represent days where there was no profit or quantity; I've tried to do this with an IFNULL function, but that did not work for me.
Thanks,
Bruce
3 replies
-
adding to the thread.
-
Hi Bruce,
It boils down to whether the cell concerned has a value, is null, or doesn't exist.
The View in Moshe's video shows Dates from his Time Dimension Table and Sales etc, from his Fact table. The Tables are left outer joined on date, so with Show Empties on, it will show all the dates in his range with empty cells where there is no corresponding date in his fact table.
In this instance, even if you test for Null, using ifnull() function, it will not return True because the cell is not null it is empty, i.e. there are no records in the database fact table that contain this date. This is because for a particular date from the date dimension there is no corresponding date in the fact table, the record does not exist. This is different from the date existing in the fact table, but there is no value for the measure (i.e, the data record exists, but the data field is null).
This can also occur if you have hierarchies on the rows and columns and the cell representing the intersection of two of the members is empty because there are no records in the database that contain a value for that combination of hierarchy values.
In this scenario, there is no way, as far as I know, to set the value to 0, because the measure does not exist for that cell.
Hope that helps,
Ian