topic Re: Display zero values in pivot table. in QlikView

Display zero values in pivot table.

dahpet03 — Thu, 07 Jun 2018 13:33:17 GMT

I need to display zero values in a pivot table and I can't find a solution that works. I've attached a very simple example: a pivot table with two dimension, month and category. Category A is missing a value for month 2017-03. So if I select A, 2017-03 won't be displayed. I would like all months to show regardless of which category is selected. However, only the months that are selected should be displayed. So always showing everything doesn't work either.

To make matters worse, adding dummy values in the data model isn't really an option. Is there any way to achive what I want without modifying the data?

Re: Display zero values in pivot table.

sunny_talwar — Thu, 07 Jun 2018 15:41:57 GMT

This might be the best thing you can do by selecting 'Show All Values' for Month dimension on the Dimensions tab of chart properties

Or you can generate missing data

Generating Missing Data In QlikView

Re: Display zero values in pivot table.

dahpet03 — Thu, 07 Jun 2018 16:01:23 GMT

Unfortunately that means I can't filter on the months, it always shows all months. Generating missing data isn't really a good option in this situation, for various reasons. One reason is that for some calculations I count distinct values of a key. I can't really add a dummy key since then it won't be zero anymore. I also can't add a column with value 1 and just sum that since then it won't be distinct anymore.

Re: Display zero values in pivot table.

sunny_talwar — Thu, 07 Jun 2018 16:06:32 GMT

You can always add a flag to differentiate between dummy data and original data and count where the flag is from original data

Re: Display zero values in pivot table.

dahpet03 — Thu, 07 Jun 2018 16:17:07 GMT

That's true. I would however prefer to solve it without generating data since I would have to generate values for all possible combinations of 5-10 fields on every date over 3 years. That sounds like a headache to me, unless I'm completely missing something.

Re: Display zero values in pivot table.

sunny_talwar — Thu, 07 Jun 2018 16:22:05 GMT

I have not came across a way to solve this problem without generating missing data. Everything I have seen is a sort of hack with its own problems. But having said that, there are many smart people on the community... and someone might be able to prove me wrong.

All the best,

Sunny

Re: Display zero values in pivot table.

dahpet03 — Thu, 07 Jun 2018 16:25:36 GMT

I appreciate you taking the time to look at it. I'll look into generating data while I keep hoping someone will come up with something clever.

Re: Display zero values in pivot table.

sunny_talwar — Thu, 07 Jun 2018 16:31:29 GMT

Sounds like a plan