Been working in Quicksight for a few months and having little issue getting a pivot table to show distinct order numbers as a zero value and looking to have all sites included even if they do not have any distinct order numbers showing null values as zero in a pivot table.
Currently I am only getting number that are greater than zero for sites that have orders and for a specified time frame. I want to show all timeframes even if there are no orders and all sites even if there are no orders (where distinct order # =null would show 0). However, we would like to have a specified filter that will only show that specific site# if selected.
I have created a Arena with a sample data. The actual visualization has parameters for date timeframe for buttons to change for Monthly, Quarterly, and Annually. Which I do not think should have any affect on this not working.
Source from another post
Also keep in mind that having a NULL value vs not having any data at all are two different things. A NULL value would be like having a row for Jan 1 2022, but the column value for Sales is empty. This is different than not having any rows for Jan 1 2022 at all. In the former scenario, you can replace the NULL with a 0 as some have pointed out on this thread (with an ifelse kind of calculation). In the latter scenario where there are no rows at all in the data, there is nothing to insersert a substitute into.
So, the work-around is to perform the count in your database / SQL and replace Null with 0 there.
I am marking this reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community!
