Handle missing value in Pivot Table

[girard_ben]

Dear All,

In a POC application I have the tested the functionality of replaced missing value by a string.

cf my attached application...I have puted "Closed" in the pivot table when there is no row

This work well in my POC !

But when I do the same in my target application, it's not working...

so if someone has an idea ?

thanks in advance

[girard_ben]

I add again my screenshot

[CarlosAMonroy]

Hi,

You can use the following expression:

=if(isnull( sum(Traffic)) or sum(Traffic)= 0,'Closed',sum(Traffic))

And/or see attached images.

Thanks,

Carlos

[sunny_talwar]

Is the attached application your target application? Can you may be elaborate on what you are looking for if what Carlos provided is not what you were looking for

[girard_ben]

Hi Carlos,

thanks a lot for your quick reply and your help but unfortunately it's not working.

be careful : when we have 0 recored in the database, the store is opened...( we have some stores with no traffic..)

(each day... Store managers have to enter the traffic in the cash registers... )

So to detect if the store is opened is to check "the missing value"...

But test after tests, I think I will handle this problem in the load script.

I explain :

     This "tips" seems to be working with a several days selected...

     For exemple if I select one store and one day with not data recorded.. qlik view will hide the store.

but If I selected two days... the first with traffin and the second one with no traffic (no row for the given store in the database) this working..

     I have one cell with an amount and the second one with "closed".

     qlikview "auto hide" elements with no data.. I'll manage this in a script load, by generated aditionnal row for "closed" days..

  thanks for your help.

[girard_ben]

Dear all ,

I have found a quick solution : to count the number or rows  if equal to 0 : the store is closed...

=if(count(Traffic)=0,'Closed', sum(Traffic))