Removing null values from a pivot table measure.

[Mapuna]

Below is my data set 

Load * Inline [
Month,Course,Score
Jan2020,AAA,30
Jan2020,BBB,10
Jan2020,CCC,40
Feb2020,AAA,55
Feb2020,CCC,45
Mar2020,DDD,10
Mar2020,AAA,15
Mar2020,BBB,60
Mar2020,CCC,18
];

Simple Pivot is my result 

Sum of Score is my measure 

Need to display '0' in the null place in pivot chart

[BrunPierre]

Select 'include zero value' under AddOn - > Data Handling

?

[Mapuna]

It is already selected. Still doesnt work

[Oliver_F]

try this: coalesce(yourExpression,0)

[salonicdk28 · Accepted solution]

Refer the below link-

https://community.qlik.com/t5/New-to-Qlik-Sense/show-quot-nothing-quot-as-Zeros-in-Pivot-table/td-p/...

[Mapuna]

By 'yourExpression' i think you mean the measure? If i got it properly it dint work too. Can you elaborate 

[Mapuna]

This works ! Great way to solve this.

But can we do this using chart in front end? 

[Oliver_F]

---

@Mapuna wrote:

By 'yourExpression' i think you mean the measure? If i got it properly it dint work too. Can you elaborate 

---

You are right, I tested it and it did not work. I guess the problem is, that the null-fields are combinations of the two dimensions that do not exists in the data model. Therefore Qlik is not even calculating anything and therefore the coalesce function is not even applied.

To make the coalesce function work you would have to make sure that all the combinations exist in the datamodel. Score can even be null() to not affect any avg() expression.

So you would have to make a full join for the dimensions and concatenate those new lines to your table.

But you already have a script-related solution and I am right now not aware of a front-end-only solution. sorry!