5 of 5 people found this helpful
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:
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
zn([MeasureField]) will do the same as well. - zn is Zero Null
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.
Can you simplify the book and show exactly where it's not working?
I tried this and it works.
IF ISNULL(ATTR([secondary datasource field])) THEN 0 ELSE SUM([secondary datasourcefield]) END