Skip to main content
The way to achieve your own success is the willingness to help somebody else. Go for it!
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Gaps in Qlik Sense Pivot table

Hi there,

I have a very basic pivot table, Hours worked per project and per level of seniority.

So, e.g.

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?

7 Replies
Partner - Specialist III
Partner - Specialist III

Hi Guus,

I've made a simple example with the data you provided, see the attached qvf.


Creator III
Creator III


Could you try to charts propretise\presentation and change default value for nul symbol and missiing symbol from '-' to '0'.

Hope helpful

Not applicable

Hi Felip,

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.

Partner - Specialist III
Partner - Specialist III

When you do the load statement, you can add something like this




     if(len([Field name]>0,[Field name],0) as [Field name]

From [xxxx];

This would fill the empty values with 0.


Not applicable

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


Project Junior Manager Support
project A01020
project B1050

And then do an unpivot operation in the database:


project A Junior0
project A Manager10
project A Support20
project B Junior10
project B Manager5
project B Support0

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!

Partner - Specialist III
Partner - Specialist III

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.

Not applicable

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 !