topic Re: Replace missing by 0 (numeric format) in pivot table in QlikView

Replace missing by 0 (numeric format) in pivot table

nicogene — Wed, 28 Jan 2015 17:14:09 GMT

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.

Re: Replace missing by 0 (numeric format) in pivot table

NickHoff — Wed, 28 Jan 2015 17:20:34 GMT

You don't need to use an if statement. In your Presentation tab there is an option to populate missing cells, the default is -, you can change it to anything you'd like.

Re: Replace missing by 0 (numeric format) in pivot table

agomes1971 — Wed, 28 Jan 2015 17:21:27 GMT

Hi,

If you go to presentation tab replace null symbol '-' for 0.

Is that what you want ?

HTH

André Gomes

Re: Replace missing by 0 (numeric format) in pivot table

ste_berbenni — Wed, 28 Jan 2015 17:22:19 GMT

Hi Nicolas,

you can open chart Properties, tab Presentation and in the bottom of the windows set "Missing symbol" like '0'

Regards,

Stefano

Re: Replace missing by 0 (numeric format) in pivot table

nicogene — Wed, 28 Jan 2015 17:34:39 GMT

Thanks for your reply.

With tab presentation, the problem is the following: it's a text format and not a numeric value.

Thanks for your return.

Kindest regards,

Nicolas.

Re: Replace missing by 0 (numeric format) in pivot table

rwunderlich — Wed, 28 Jan 2015 22:20:52 GMT

On the Presentation tab, select the field and then "right" for text alignment.

-Rob

Re: Replace missing by 0 (numeric format) in pivot table

nicogene — Thu, 29 Jan 2015 06:12:30 GMT

Thanks for your response.

But when I realize an export to MS Excel, the format is text. I would like

to replace missing value by numeric value.

Kindest regards,

Nicolas.

Le mercredi 28 janvier 2015, Rob Wunderlich <qcwebmaster@qlikview.com> a

écrit :

Re: Replace missing by 0 (numeric format) in pivot table

Anonymous — Thu, 29 Jan 2015 06:45:01 GMT

Hi Nicolas

Actually when you checked suppress Zero Values . Qlikview consider 0 as null or missing. So just uncheck the option it will work.

Let em know if you are looking for something else.

Re: Replace missing by 0 (numeric format) in pivot table

Thu, 29 Jan 2015 07:56:33 GMT

Hi Nicolas,

PFA.

Thanks,

Chiru

Re: Replace missing by 0 (numeric format) in pivot table

Anonymous — Thu, 29 Jan 2015 08:09:55 GMT

Sorry forget to attach the solution.

PFA

Re: Replace missing by 0 (numeric format) in pivot table

nicogene — Thu, 29 Jan 2015 14:42:36 GMT

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

Kindest regards,

Nicolas.

Re: Replace missing by 0 (numeric format) in pivot table

rubenmarin — Thu, 29 Jan 2015 14:52:00 GMT

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')

Re: Replace missing by 0 (numeric format) in pivot table

agigliotti — Thu, 06 Oct 2016 07:57:53 GMT

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

Re: Replace missing by 0 (numeric format) in pivot table

agigliotti — Thu, 06 Oct 2016 08:01:10 GMT

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 ?

Re: Replace missing by 0 (numeric format) in pivot table

agigliotti — Thu, 06 Oct 2016 08:20:39 GMT

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.