Announcements
cancel
Showing results for
Did you mean:
Creator

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

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?

24 Replies

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.

talk is cheap, supply exceeds demand
Creator
Author

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.

Creator
Author

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

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

Specialist III

I always len (etc ) not isnull

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

NULL handling in QlikView

Partner - Creator II

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

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

talk is cheap, supply exceeds demand
Creator
Author

This is not working too

Creator
Author

Not working

Please post a small qlikview document that demonstrates the problem.

talk is cheap, supply exceeds demand