If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Dividing two (same) measures from different hierarchy levels

Started by hpmkruit, 10 Sep 2009 02:48:51 AM

Previous topic - Next topic

hpmkruit

Hello all, hopefully you can help me with this:

I have a report with a crosstab. This one consists of a division dimension on the left side and a time dimension on the top side. I have inserted different levels of the time dimension, like 'Last year' and 'This year'.
Under these time dimension levels i have inserted different measures like 'Budget', 'Realisation', 'Difference Between Budget and Realisation'. This looks somewhat like the following:

             'Last year'    'This year'     
             'Measures'     'Measures'    'Extra calculation column'
Div. A
Div. B
Div. C
...
Total

Now what i want to do is add an extra column next to 'This year' which takes the 'Difference between Budget and Realisation' from 'This year' and divides it by the same measure of 'Last year'.

What i have tried:
Select both measures and Insert Calculation. Specify /, but then it somehow takes the same measure each time and all column rows are filled with '1'.

Build two other queries which take the measure sliced by 'Last year' and the other by 'This year'. Then add a query calculation in the crosstab which divides both sliced measures. Each query alone gives the correct result, both the query calculation gives me just '1' 's again.

Any help would be great, because this problem occurs in multiple report.

billylodz

add data item with calculation ([Last Year] - [Current Year])

and use it in crostab it te same way than [Last Year] and [Current Year]
mean nest measures

hpmkruit

I got what you were saying, and it's awesome. Created extra data item [This year] / [Last year]. The nested it like so:

              'Last year'    'This year'             'Measure'
             'Measures'     'Measures'     '[This year] / [Last year]'
Div. A
Div. B
Div. C
...
Total

Thanks a bunch!