I have been trying to use / replace null with 0 but that’s not working. Have tried all the ways round, I can’t do it in the dataset, so is it possible to fix it please. Sample screenshot attached.
Also please introduce union via 2 different data sources in the quicksight.
Hi,
you can use QS function in you formula. nullIf(delayReason, ‘unknown’)
Today, the setting only affects presentation of fields that are placed into Rows and Columns. It is not used if field is placed into Values field well.
At the moment, if you want to see zeros instead of empty cells in the table or pivot table values, you need to change the expression to return zero. This might even require changing the dataset if the data is sparse.
I expect QuickSight team to provide more options when configuring tables so that you can customize the value if it is not present however this work has not been prioritized yet. (This work impacts not only the presentation of the data but the calculations as well. When you want to show 0 for missing rows, it should impact totals and subtotals calculations. For example, average of [0,1,2] is different from the average of [1,2]. )
Ok and what is the reason for not exposing unions to the community in QS?
And what is the reason one can’t run sql on top of custom sql data set which can be joined from 2 different data sources?
Please put good priorities.
Hi Tatyana, can you tell me when this is in priority? Because right now I can not see the percentage difference in a table when it goes to zero (null), this is a key signal I want to display in the dashboard.
Thanks
It doesn’t work either. I add a calculated field avg_adoption as you say to use in a two-dimension table.
Definition of avg_adoption is as the leftmost pic.
And config null values shown as custom as the middle pic.
However, I use avg_adoption as values in field wells, but it doesn’t display ‘-’ as I expect. I’m sure adoption is 100 in MoM column and avg_adoption should be null. As the rightmost pic.
Hi @winfield,
As Tatyana already mentioned you will not be able to use Show Custom on columns in Value field well. You will have to return that particular value from your calculation, For Instance, if the value is NULL then you will pass back 0 which will then be used as part of sum/avg/ any other aggregation type you are intending to use.
In the case of avg, as Tatyana called out it will not be true average for example avg(0,100,200) is 100 where as the calculation should truly be avg(100,200) that comes out to 150.
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.
QuickSight (and most BI tools) being read-only, we cannot insert new rows of data (can only make new columns). I think this is where your question about doing UNIONS in the data prep is probably coming from? You are right that would be a solution to the latter scenario (to insert a row with a null or 0 value), but as other mentioned the only want to to a UNION is by using Custom SQL. Yes, we are looking at adding UNIONS into the data prep UI as well, but unfortunately I cannot comment on dates in this forum 
