Calculations on Null Values

[erjorgea]

Hi everybody,

I have a calculation member that sum some measure,  but if one of the measures has no value (is null) the sum is not done correctly.

For example, measures are: measure_1, measure_2 and measure_3.

I do not want to do for every measure check if is null like this:
case
       when (measure_1 is null then measure_2+measure_3
       when (measure_2 is null then measure_1+measure_3
       ...
end

Exist any other option?

Thanks

[BigChris]

Could you use coalesce?

coalesce([measure_1],0) + coalesce([measure_2],0) + coalesce([measure_3,0)

The more elegant method would be to substitute nulls for 0 at source, but I don't know if that's an option

[erjorgea]

Hi,

I try what you do, but it does not working, gives me an error, I use dimensional measures, so is possible that not work fot this reason?

Regards

[bus_pass_man]

Without knowing what error you are encountering, my surmise is that, because Coalese isn't a dimension function, there's an error complaining about syntax or something like that, possibly with squiggly lines under coalese.   

Instead of using coalese, try something like this, which will work with dimensional sources.   I have both the nominator and denominator handling for nulls just for illustrative purposes.

If( [Return Quantity] is null ) then (0) else ( [Return Quantity])
/
If( [Quantity] is null ) then (0) else ( [Quantity])

Also, it is possible that your dimension source has built-in support for treating nulls.  For example, Transformer allows you to set a property on measures to treat nulls as zeros.

[erjorgea]

Hi measure is always completo values, not have null values, the problem is that I divided this measure in some dimension, but  does not have all these dimensions have product correspondence.
I put an example: for example my measure is called Item, this item can be sort in diferent types, A, B and C.
Imagine that in one year I hace 100 items, 60 of type A  and rest of type B, so I not have items of type C. That is the problem I have.

I know I can wotk with the option case when for every type, but is too long when have 4 or 5 types.


Existe any other option?

Regards

[Francis aka khayman]

care to share how you divided the measure in some dimension?

[srmoure]

I am experiencing a similar issue after upgrading from 10 to 11 and SQL 2008 to SQL 2007).  I had a crosstab (dimensional source) with monthly sales by country.  Months in the columns and the 2 countries being Australia and NZ in the rows.  To get the total I selected both countries to obtain Australia + NZ and the total sales for Oceania.  This was working fine before the upgrade. Now it only works for the months where both countries have sales.  When there's a null in one month for 1 country then the total is Null. In the old server (C10) the report is working fine, but I need a method to overcome the issue in CA11.

Thanks

EDIT: I found a solution to this issue

Treat Nulls as Zeros within Calculations
By default, the Dynamic Query Mode propagates nulls in calculations. However, it is possible to override the default behaviour for DMR packages through configuration settings similar to those for pure OLAP data sources.
Impacts: Calculations on data items that contain null data values.
Usage: This set of parameters controls whether or not null data values are treated as zeros when used in calculations. If the parameters are enabled, 100 + null would result in 100. If the parameters are disabled, 100 + null would result in null. By default, these parameters are disabled.
Interoperability with other parameters: None
Setting these parameters: The parameters are available within the file c10\configuration\xqe\dmr.properties as shown here (with the default settings):
null.plus.operator=null
null.minus.operator=null
null.multiply.operator=null
null.divide.numerator=null
null.divide.denominator=null
null.modulo.dividend=null
null.modulo.divisor=null
Show less
To enable this feature, change the null values to zero as follows:
null.plus.operator=zero
null.minus.operator=zero
null.multiply.operator=zero
null.divide.numerator=zero
null.divide.denominator=zero
null.modulo.dividend=zero
null.modulo.divisor=zero
Show less
These changes will be picked up once the IBM Cognos 10 service is restarted. After the restart, this change will affect all queries against any DMR data source through IBM Cognos 10. In a distributed environment, this change will need to be made on all IBM Cognos 10 servers performing data access.