Pivot Tables, Calculated Fields and Null Values

[jackattack6800]

Working within a pivot table. Using count(resourceID) calculated field to determine if a given AWS service was used during that period. Goal is to have green/red traffic light rather than actual count.

How do I properly format the null value? I have seen reference to coalesce but it doesn’t seem to be returning a zero.

[Naveed]

Hi,

Try this one, It will fix it.

How to show Zeros in the pivot table?

We had a similar situation, where we wanted to show zero sales for a day for a given product. We solved it by using a calendar table in our SQL. This calendar table has one row in it for every day. It also has extra attributes, such as day of week, day of year, week of year, day name (sun, mon, etc), month name, month abbr, month id (1-12), etc. so our sql had a left outer join from the calendar table to the sales table, such as select c.date, sum(coalesce (s.sales_qty, 0)) as sales_qty …

Regards,
Naveed Ali

[jackattack6800]

Thanks. Still looking for a simpler solution. I see many posts about handling this very issue with little success. Seems like Quicksight needs to put some work into an enhancement for this.

[Xclipse]

Hi @jackattack6800, your feedback helps us build a better service. I have tagged this as a feature request. More features are being added on a regular basis, so please keep an eye on the What’s New / Blog . You can set up a Watching Alert by clicking on the bell icon.