Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
dahpet03
Contributor II
Contributor II

Display zero values in pivot table.

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?

7 Replies
sunny_talwar

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

Capture.PNG

Or you can generate missing data

Generating Missing Data In QlikView

dahpet03
Contributor II
Contributor II
Author

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.

sunny_talwar

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

dahpet03
Contributor II
Contributor II
Author

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.

sunny_talwar

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

dahpet03
Contributor II
Contributor II
Author

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.

sunny_talwar

Sounds like a plan