Hi Cognos Gurus,
I need some help/guidance with the below requirement:
-I need to get the % calculation of the following data items Value1($), Value2($), Overlap($)
-Value1($) data item was came from Query1
-Value2($) data item was came from Query2
-Overlap($) data item was the results of the joined two queries above (Query1 & Query2) using equal join (1..1)
-I made a UNION of all these (3) queries and use the union query in my report where the below calculations for % was also created.
--To get the % of Overlap($) with Value1($) I do the following expression ([Overlap($)]/[Value1($)])
--To get the % of Overlap($) with Value2($) I do the following expression ([Overlap($)]/[Value2($)])
I tried every possible Aggregate Function and Rollup Aggregate Functions but it seems that I always got a 100% value in %column.
I also tried applying Solve Oder value (1 or 2) and returns the same value (100%) in %column.
Thanks!!
obviously you are using relational but i'll say it explicitly just to be clear :)
what i know is that to correctly compute two data items, they have to be in a single row:
Dim1 Dim2 Value1 Overlap
x y 30 15
then you can properly compute:
Overlap/Value1
Quote from: khayman on 20 Jun 2014 02:41:21 AM
obviously you are using relational but i'll say it explicitly just to be clear :)
what i know is that to correctly compute two data items, they have to be in a single row:
Dim1 Dim2 Value1 Overlap
x y 30 15
then you can properly compute:
Overlap/Value1
Well said khayman. Let me take it a step further and say that you are suggestion a join between the queries rather than a union. Having said that, a union can work, but you might need some trickery like: Overlap/Total (Value1 for Dimension1ID)
Are Value1 or 2 already aggregated? if is not specified for what is aggregated will give the same results with the overlap one