I have a very basic pivot table, Hours worked per project and per level of seniority.
Project Manager Junior Support
project A 10 - 20
project B 5 10 -
The dashes are gaps, there are no records with hours for a junior for project A.
I do a conditional formatting on the values: < 10 Red and >= 10 Green.
The gaps are not influenced by my formatting script.
Is it possible to join the gaps in the conditional formatting and to display 0 instead of a dash?
The pivoting is done in the Pivot table.
Data is imported from:
Project, Seniority, Amount
project A, Manager, 10
project A, Support, 20
project B, Manager, 5
project B, Junior, 10
So, 4 records to fill 6 amounts, the missing 2 are the gaps.
When you do the load statement, you can add something like this
if(len([Field name]>0,[Field name],0) as [Field name]
This would fill the empty values with 0.
The thing is:
I have 6 data points to fill with 4 records, so 2 gaps.
When I select only project A, I don't have a gap, there is no value for Junior, it is not displayed.
Project Manager Support
project A 10 20
The only thing I can think of is doing a pivot operation on my database view:
Project Junior Manager Support
project A 10 20
project B 10 5
Fill the gaps with zeroes
And then do an unpivot operation in the database:
Now two new rows have been created.
Unfortunately, my table of 6 million rows has now doubled to 12 million and I hope Qlik sense can handle this!
Attached the qvf with one solution.
You must keep in mind that this will add some rows and if you have a lot of combinations that will expand your base quite quickly.
I got it working with the method I discussed with Felip Drechsler.
But adding records with value 0 is a very artificial solution.
I cannot find the option which you suggest, to change the way NULL is displayed.
Is there a way to do this in Qlik Sense?
All the answers I find when I google this problem refer to Qlik View, not Sense !