In Qliksense, can we show '0' instead of '-' in pivot tables when there are no such records?

[rvijayanth]

In Qliksense, in pivot tables, instead of '-'s, can we show '0's in place of null records?

For example, we have a pivot table with dimensions Year and Category and Count as measure.

Consider this data set:

Counts for 2016 for Category A is 10, B is 2 and C is 7

Counts for 2015 for Category A is   6, B is 3 and C is 0 (since category was established only in 2016)

So our pivot table shows this:

Year             Category A      Category B        Category C

2016                           10                   2                       7 

2015                            6                    3         -

Instead, I want it to be like this: See how for 2015, category C says 0 instead of '-'

Year             Category A      Category B        Category C

2016                         10                    2                       7 

2015                           6                    3                       0

How to accomplish this?

[Gysbert_Wassenaar]

No, missing data cannot be shown as 0's. If you want that you'll have to add dummy records with 0's to the data in the script.

[rvijayanth]

We cannot add zeros. These counts have to happen in the fly and since its millions of rows for random combination, its not possible to write zeroes.

[rvijayanth]

Why isn't something like this in the measure working?

if (isnull(Count( distinct orders)) = '1' , '0', Count( distinct orders))

[robert99]

I always len (etc ) not isnull

say if (len(Count( distinct orders)) = 0 , 0, Count( distinct orders))

ahve you read this

NULL handling in QlikView

[kangaroomac]

Vijayanth,

Robert's suggestion should work.

The reason why your expression won't work I assume could be because you are trying to do a "double comparison".

the If(IsNull(...)... is your comparison test, the = '1' part is ambiguous.

i.e. you can use:

If(IsNull(Count(Distinct Orders)), 0, Count(Distinct Orders))

[Gysbert_Wassenaar]

Because if count(distinct orders) is null then the isnull function returns -1 not '1'. So this will work:

If(IsNull( Count( distinct orders)), 0, Count( distinct orders))

[rvijayanth]

This is not working too

[rvijayanth]

Not working

[Gysbert_Wassenaar]

Please post a small qlikview document that demonstrates the problem.