How to show Zeros in the pivot table where there is no data for Specific Months

tdr_Dinesh · July 31, 2024, 5:25am

Hello Team,

Could you some one assist us on below scenario.

If we look at the visualization below, we have pulled data for January to July. However, March and April do not show. We want to see these months in the chart, even though the data fields would be blank. We need to add columns for the missing months, either with zeros or blanks. Showing zeros will be most helpful.

We have tried with different scenarios,

By using the formatting fields option, I changed the null values to ‘0’. I also tried using the formula below, but we are getting the following error message.

ifelse((count(Value field))=“NULL”,0,(count(value field)))

Data Source - Athena

Naveed · July 31, 2024, 6:05am

Hi,

How are you ?

Just click the dropdown on the field/column you want to added from your visual.
Search for the “Format” and check on the option “More formatting options”. You can set any value in case of null.

Regards,
Naveed Ali

tdr_Dinesh · July 31, 2024, 7:29am

Hi @Naveed ,

I am doing well, thanks for asking. I hope you are doing well too.

I tried the option you shared, but unfortunately, it didn’t work.

Shahid_Muhammad · July 31, 2024, 7:41am

Hi
Most probably, it’s due to no data available for that particular month in your data set. Check your dataset if you have entry of April and may respectively.

tdr_Dinesh · July 31, 2024, 9:10am

Hi @Shahid_Muhammad ,

yes, data is available for all the months from January 2024 to till date.

Thanks!

Naveed · August 1, 2024, 5:07am

Hi,

You can try it with calculated field with coalesce(value,0)

Hope this will work for you.

Regards,
Naveed Ali

tdr_Dinesh · August 1, 2024, 12:04pm

Hi @Naveed ,

I have tried the above formula, but I haven’t seen any changes. I have created a sample test case in Arena, and below is the link for your reference. Please let me know where I might have missed something. Note: I have applied a couple of filters to the visualization.

Testing Analysis

Thank you!