6 Replies Latest reply on Oct 16, 2018 10:07 AM by ketki.borawake.0

# Calculating on Null Value

Hello,

Does anyone know if/what filters need to be adjusted so that null values are calculated within a calculated field.

Have created a table calculation where the sum of %MV is calculated against the 'First'

Ordered as a table calculation, blank values are assumed to be 0.00, and all fields are applied in the calculation.

Though as soon as a parameter is introduced to run a calculated field 'PLAY; find that %MV values that were blank are ignored in the calculated field. Hoping to have all values (including blanks) calculated upon, does anyone know if/how my calculated field OR filter settings could be adjusted.

• ###### 1. Re: Calculating on Null Value

Hi Ryan,

By default, NULL does NOT signify a value of zero, but rather the absence of any data with which to compute a calculation.  A simple example is if you have values of 10, 20, 30 and NULL, the AVG function will compute (10+20+30) / 3 = 20 and NOT (10+20+30+0) / 4 = 15

To force the calculation to treat NULL as 0 and achieve the 15 with the AVG function, you could create a calculated field as:
IFNULL([MeasureField],0)

Then, use this alternate field in your view instead of the original measure with NULL values and your calculations and aggregations should treat the NULL values as 0.

Hope this helps!

6 of 6 people found this helpful
• ###### 2. Re: Calculating on Null Value

zn([MeasureField]) will do the same as well. - zn is Zero Null

8 of 8 people found this helpful
• ###### 3. Re: Calculating on Null Value

Thanks Mark, Alex,

Have ordered both formulas on my [measure], though perhaps not applying it correctly, calculated fields are continue to ignore these (blank) fields.

Have applied the following calculations. in case misappropriated, can either correct my application.

= IFNULL([%MV],0)

= zn([%MV])

Thanks again,

• ###### 4. Re: Calculating on Null Value

Can you simplify the book and show exactly where it's not working?

• ###### 5. Re: Calculating on Null Value

I tried this and it works.

IF ISNULL(ATTR([secondary datasource field])) THEN 0 ELSE SUM([secondary datasourcefield]) END

• ###### 6. Re: Calculating on Null Value

none of the above formula is working for me.

I want to calculate AVG Qty of all Months and while calculating its not considering null values.

so here I want grant total(AVG) for first row to be 0.16

is there any other way of doing this?