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