Skip to main content
Announcements
Accelerate Your Success: Fuel your data and AI journey with the right services, delivered by our experts. Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
Mapuna
Partner - Contributor III
Partner - Contributor III

Removing null values from a pivot table measure.

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 

AnupamRaj_0-1672817746484.jpeg

Sum of Score is my measure 

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

Labels (1)
1 Solution
7 Replies
BrunPierre
Partner - Master
Partner - Master

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

?

Mapuna
Partner - Contributor III
Partner - Contributor III
Author

It is already selected. Still doesnt work

Oliver_F
Partner - Creator III
Partner - Creator III

try this: coalesce(yourExpression,0)

Mapuna
Partner - Contributor III
Partner - Contributor III
Author

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

Mapuna
Partner - Contributor III
Partner - Contributor III
Author

This works ! Great way to solve this.

But can we do this using chart in front end? 

Oliver_F
Partner - Creator III
Partner - Creator III


@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!