Replace missing by 0 (numeric format) in pivot table

[nicogene]

Dear all,

In a pivot table, I would like to replace missing value by 0 (numeric) but it doesn’t work.

Below my formula:

=num(if(IsNull( Count(Distinct SKey)),0,Count(Distinct SKey)),'# ##0')

Many thanks in advance for your help.

PS: a .qvw file is available in attachment.

Kindest regards,

Nicolas.

[nicogene]

Thanks but it doesn't work for avg(sales).

Kindest regards,

Nicolas.

[rubenmarin · Accepted solution]

Hi, chandel is right, but you need to apply the same logic you are using in transactions expression to the avg expression:

=num(if(IsNull(Sum(Invoiced)/Count(Distinct SKey)),0,Sum(Invoiced)/Count(Distinct SKey)),'# ##0')

[agigliotti]

With that option unchecked qlikview ignore the set analysis used in the expression.

[agigliotti]

using the if condition I got the zero as number but as I replied to chandel I need to use set analysis within expression.

is there any way to achieve it ?

[agigliotti]

Hi,

I have the same needs but I'm using set analysis within expression.

It doesn't works because the set analysis, with the settings suggested by chandel (uncheck suppress zero values), is being ignored.