Percentage (%) Column return 100% for all rows

[Web2CRM]

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!!

[Francis aka khayman]

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

[MDXpressor]

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)

[orbair]

Are Value1 or 2 already aggregated? if is not specified for what is aggregated will give the same results with the overlap one