member1-member2 does not work when member2 has no values

[sergiord2016]

Hi ,

I have a crosstab with one member as a column , other member as another column and a third column with the auto calculated column  member1-member2

This works for each case where the member 1 and meber 2 has values , but for example if the member1 has the value 10 and the member2 has no values the result of the calculated column is empty string.

I tried changing the proporties of ecach member , asigning 0 for empty string values but the result is 0 and not 10. It seems that if the member2 has no values the substract operation does not work.

Please anyone can help me?

Thank you in advance.

[New_Guy]

Hi,

Which cubes are you using? we have TM1 cubes and we never had this issues. I tried with transformer cube and I am having the same issue too.
Tried using tuples too but still the same result.

Good luck
New Guy

[sergiord2016]

Hi

How can i verify the type of cube?

[New_Guy]

Hi,
You should be able to see [PC]  or [TM] before the member. PC is for transformer power cubes and TM is for TM1 cubes.
Rgards
New Guy

[XXXX].[XXXXX].[XXXXX].[XXXXXX]->:[PC].[@MEMBER].[XXXXXX]

[sergiord2016]

[BBDD_T].[SERVICIO].[AGRUPA SERVICIOS].[SERVICIO]->[all].[M].[MIR]

This is an example ...

[sergiord2016]

any opinion?

i thought it had an easy solution...

i want to substract two members even when one member is null substract as it was zero value.

how can i do this please???

[New_Guy]

Check the below link.
https://www.linkedin.com/pulse/calculations-null-values-cognos-report-studio-advanced-craig-eskdale
Good luck
New Guy

[sergiord2016]

That should work.

I will probe and tell you , thanks!

[sergiord2016]

HI ,

It does not work , it shows an error , i translate from spanish:

The argument in position 2 of the coalesce of data element DIF_ACUM_VER , is a `member` expession that can not be converted to a value expression


coalesce( [ACUMULADO AÑO ACTUAL VER] - [ACUMULADO AÑO ANTERIOR VER];[ACUMULADO AÑO ACTUAL VER] ; -[ACUMULADO AÑO ANTERIOR VER])

Do you know whats the preoblem here?

[sergiord2016]

please help!

[Deep750]

Coalesce is a relational function, don't hink you can use it against cube.

Try to make a CASE or IF-THEN-ELSE statement.
Something like
if(member1 is null)
THEN (member2)
else if (member2 is null)
then (member1)
else (member1-member2)

[sergiord2016]

Yes!

That works!!!

Thank you very very much