4 Replies Latest reply: May 29, 2015 10:20 AM by Ashley Navin

# 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?

• ###### 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()

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

• ###### 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