Showing 0s in pivot table for data that doesn't exist

[glam]

Hi,

I currently have a pivot table with date, name and count. The count is based off of how many times the name shows up in the dataset with that date. If the name doesn’t exist on that date, the pivot table shows blank. Is there a way to show 0?

[Brett]

Hi @glam,
Here are a couple prior posts in the community that discuss a similar scenario for your review:

Replace blank/NULL values by 0

Hi! I’m creating a WBR visual with the Receive Accuracy metric. The logic for RA metric is= POs correctly received/total POs There are some weeks where we don’t have any POs delivered in specific regions, so we have rows for that week with null values. [RA_wbr] I want to substitute those null values by ‘N/A’. I first tried to edit format data for null values, but it’s not working. Then I tried the following calculated field formula, but it gives me error and I’m not able to save it: [ra_form…

Replacing Numeric Column Blank Data With 0

PFB the screen shots of a table column data. I want to replace the blank column into zero so how can I do this? [Numeric Column]

Let us know if one of these suggested solutions work for your case or if you have any additional questions.

[glam]

Hi Brett,

Thanks for reviewing. I have tried using the methods in sent but have no luck. See code below as it seems to work, but still not showing 0 in the pivot table. Any additional thoughts?

ifelse(isNotNull(distinct_countIf({Name},{Country}=“France”)),distinct_countIf({Name},{Country}=“France”),0)

[Brett]

Hi @glam,
So when you add this new calculated field to the pivot table, does it change the visual at all..is anything different occurring in those boxes or is it showing blank as it was prior to the switch?

Re-Reading the first post shared, titled ‘Replace blank/NULL values by 0’..you may need to incorporate ‘toString’.

This may be easier to assist further if you’re able to upload a copy of your analysis, with anonymized data, on to the QuickSight Arena view, that way we can test out a few different scenarios to see what may work in this case!

QuickSight Arena -- An embedded instance of QuickSight within the QuickSight Community

[FeatureTile 11(2)] Watch this video for a 30 second intro to Arena. QuickSight Arena provides you with an embedded instance of QuickSight right within the QuickSight Community. Using Arena, you can: Explore creating QuickSight data visualization dashboards for free Publish dashboards to the Community Gallery to showcase your skills Include dashboards depicting your use case while posting questions in the Community Q&A Clone underlying analysis views of published dashboards in order …

[glam]

Thanks Brett - I was able to find a solution - appreciate the help with the above