Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

rvijayanth
New Contributor III

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?

22 Replies

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

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
rvijayanth
New Contributor III

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

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
New Contributor III

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

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

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

robert99
Valued Contributor II

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

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
Contributor II

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

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

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

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
rvijayanth
New Contributor III

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

This is not working too

rvijayanth
New Contributor III

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

Not working

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

Please post a small qlikview document that demonstrates the problem.


talk is cheap, supply exceeds demand
Community Browser