Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Add row of zeroes where dimension doesn't exist

I am using a pivot table with two dimensions. The second dimension is a calculated dimension that is grouping the data into 3 categories. The problem I am having is that for some of the top dimension values there aren't always all 3 categories in the second dimension. I would like to display all 3 possible second dimension values for each first dimension value regardless of whether there are any rows in my data set that have that first dimension and second dimension combo - just put zeroes where this is the case. Is this possible?

4 Replies
Luminary
Luminary

Re: Add row of zeroes where dimension doesn't exist

What is your expression? Try playing around with your dimension & chart properties (Display all values/deactivate any suppress nulls/0's).

Re: Add row of zeroes where dimension doesn't exist

Try to use Dimensionality in your IF statement

Look for more detail here

How to use - Dimensionality()

danieloberbilli
Valued Contributor II

Re: Add row of zeroes where dimension doesn't exist

Pivot tables generally suppress missing values - sometimes it works depending on the data, dimension and expressions

E.g. try to use this as an expression to 'write/force' a 0 in the table...unfortunately not a universal solution

orig:

sum(F1)

try:

if(sum(F1)=0,0,sum(F1))

anavin33
New Contributor III

Re: Add row of zeroes where dimension doesn't exist

if(hold_1 = '043' or hold_2 = '043' or hold_3 = '043' or hold_4 = '043' or hold_5 = '043' or other= '043', 'ment', If(cd = 'DD' or cd = 'SV', 'Core', 'Cert'))

If this is the if-statement I am using, how do I get the dimension 'ment' to appear even when it doesn't satisfy the if-statement... I would like 'ment' to be displayed with all zeroes if it does not satisfy the if-statement