topic Re: Removing null values from a pivot table measure. in App Development

Removing null values from a pivot table measure.

Mapuna — Wed, 04 Jan 2023 07:37:20 GMT

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

Re: Removing null values from a pivot table measure.

BrunPierre — Wed, 04 Jan 2023 08:08:21 GMT

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

Re: Removing null values from a pivot table measure.

Mapuna — Wed, 04 Jan 2023 09:16:17 GMT

It is already selected. Still doesnt work

Re: Removing null values from a pivot table measure.

Oliver_F — Wed, 04 Jan 2023 09:28:25 GMT

try this: coalesce(yourExpression,0)

Re: Removing null values from a pivot table measure.

salonicdk28 — Wed, 04 Jan 2023 09:54:10 GMT

Refer the below link-

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

Re: Removing null values from a pivot table measure.

Mapuna — Wed, 04 Jan 2023 10:06:22 GMT

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

Re: Removing null values from a pivot table measure.

Mapuna — Wed, 04 Jan 2023 10:30:50 GMT

This works ! Great way to solve this.

But can we do this using chart in front end?

Re: Removing null values from a pivot table measure.

Oliver_F — Wed, 04 Jan 2023 10:50:40 GMT

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