I have multiple pivot tables that are using the table calculation of “difference” for the values since I am comparing day-over-day change. However, I have issues when data doesn’t exist for a day since the difference for the next day gets messed up comparing to no data present (rather than a zero value). Is there a way in Quick Sight to calculate the difference from a blank data point? I figure I would have to create dummy data in my query for every day to forcefully populate zero, but I would benefit greatly if Quick Sight could do it automatically.
Hello @strellis , welcome to the Quicksight Community!
My suggestion, if you can not change this using SQL on your dataset, would be to create a calculated field using ifelse.
Something like this:
ifelse(
{sales} = 'NULL' OR {sales} = ' ',
0,
{sales}
)
This would replace the field that you have in your pivot table. Let me know if this helps or throws an error!
Hi Duncan, thank you! I have tried this solution before. The recurring issue I get is that I have another calculated row for my pivot table rows. For example, I have rows of dollar tiers for the sales. So, if I am trying to compare day-to-day sales, this gives me a difference overall. However, I have dollar tiers of $1K+, $5K+, and $10K+. So, even with this formula, the $10K+ row is still blank and therefore cannot do the difference. Have any suggestions for that?
Hello @strellis !
Ultimately, once you are in the Analysis layer, Quicksight is not great at manipulating the underlying dataset except to add calculations/aggregations on top of it.
One thing you could try is formatting the value fields to replace null values with a “Custom” value. That being said, I’m not sure if it will have an effect on the table calculations.
Go to your value field well and click the three dots next to the metric and click into More formatting options:
In the next panel, change your Null vales option:
Let me know if this helps!
Hello @strellis !
Were you able to test whether the table calculations will accept a custom value from the formatting options I mentioned above?