Hello everyone.
Just wanted to know whether I can assign a default value to blank cells on the Pivot Table.
As you can see due to unavilability of data, blank cells are apearing. I want to fill these blank cells with ‘0.00’ values.
Appreciate if someone can help me.
Hi
Can you try adding a calculated filed which checks for null values and replaces them if they are null -
Unfortunated, since there is no data for some time belts, calculation does not compute.
Can you share the error you get while applying the isNull Calculated field to your dataset where values are null ?
I’m not getting an error, since there is no data for certain channels for certain timebelts, calculation does not apply for those cells. Hence same ‘blank’ cells are apearing even after applying the isNull condition.
Have you tried substituting the null values with the default value using an ‘if’ value is null , substitute with default value condition?
Similar Excerpt from isNull - Amazon QuickSight :
ifelse( isNull({ActiveFlag}) , ‘Inactive’, ‘Active’)
Yes I used the below calculation.
ifelse( isNull({TVR}), ‘0.00’, {TVR})
However, since there is no data for certain time belts, above calculation doesn’t apply.
For example, in the above chart, if you look at the Channel in the fist column, it has data only for 08:00 to 09:15 time belts.
My dataset looks like as follows. There are gaps in between certain time periods.
| Time | Channel | Viewership |
|---|---|---|
| 8:00 | A | 1 |
| 8:01 | A | 1 |
| 8:02 | A | 1 |
| 9:20 | B | 1 |
| 9:21 | B | 1 |
| 9:22 | B | 1 |
| 9:23 | B | 1 |
| 7:00 | B | 1 |
| 7:01 | B | 1 |
hi @PandaDH ,
You can do this by using coalesce function in QuickSight.
E.g - coalesce({TVR%},0.00)
Please revert back if you are facing any challenges.
Thanks & Regards
Biswajit Dash
Hi @Biswajit_1993 , thanks for the suggestion. However, I’m still not getting the correct report since it still shows balnk cells.
Looks like the function is only apply if there is data for a given time belt.
hi @PandaDH,
since there is no data for the time belt, I don’t think creating calc filed resolve. I have tagged this as a feature request. Your feedback helps us build a better service.
kind regards,
Wakana
