Discussion board where members can get started with Qlik Sense.
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 '-'
2016 10 2 7
2015 6 3 0
How to accomplish this?
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.
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.
Why isn't something like this in the measure working?
if (isnull(Count( distinct orders)) = '1' , '0', Count( distinct orders))
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
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))
This is not working too
Please post a small qlikview document that demonstrates the problem.