Hi all,
I have to sum 5 measures considered from 5 different fact tables.
So, because a stich query is produced, i need to use the coalesce function and I create in my report query the following elements as follows:
x = coalesce(a +b , a , b)
y = coalesce (c +d, c , d)
z = coalesce ( x + y, x , y)
FINAL = coalesce(z + e , z, e)
where a,b,c,d,e are my measures and x,y,z are intermediate calculation.
FINAL is the final result of the sum
But, using this method, FINAL sometimes is null even if "z" or "e" is not null
What's wrong?
How can I sum these 5 measures easily?
Thanks in advance
UPDATE:
The issue is not due to coalesce but it is related to the transformation from list to crosstab.
In fact, If I perform the caculation using a list all works well.
But, when I transform this list in a crosstab using the related button in the toolbar, the calculation is wrong!!
I'm using Cognos BI 10.2 FP4 and DQM)
The issue is similar to
http://www-01.ibm.com/support/docview.wss?uid=swg1PI06115
Converting the project from DQM in CQM all works well!!
I will open a PMR to know if exists a workaround in oder to use DQM mode.
I hope this post will be useful to others.