Hi All,
Got something I'm not sure we can fix, maybe I have to change the data input format, since that is not easy lets give it a try in this community first:
In the data there are 2 columns that need calculations such as '-/-' and ' / '. These columns are named 'Scenario' and 'Variable'.
'Scenario' contains values like 'Actual 1112' and 'Actual 1213'.
We need to calculate 'Actual 1213' / 'Actual 1112' as 'Index Actual vs Actual LY'.
'Variable' contains values like 'Revenue' and 'Costs'.
We need to calculate 'Cost' / 'Revenue' as 'Costs %' and
we need to calculate 'Revenue' -/- 'Costs' aas 'Net Revenue'.
We need to make the output look like this (in red what is calculated):
| P&L part | Actual 1112 | Actual 1213 | Index Actual vs Actual LY |
|---|
| Volume | 100 | 110 | = 110 / 100 = 1.1 |
| Revenue | 1.000 | 1.200 | = 1.200 / 1.000 = 1.2 |
| Costs | 100 | 100 | = 100 / 100 = 1 |
| Net Revenue | = 1.000 -/- 100 = 900 | = 1.200 -/- 100 = 1.100 | = 1.100 / 900 = 1.22 |
| Costs % | = 100 / 1.000 = 10% | = 100 / 1.200 = 8,33% | = 8,33% / 10% = 0.83 |
What we did so far is that we calculated via Set Analyses as Expressions the different P&L parts (Volume, Revenue, Costs, Net Revenue, Costs %). As Dimension we took Scenario. We selected a pivot table as output form and switched the Expressions with the Dimension.
Now we can calculate al the P&L parts or Variables we need, but:
1) We still can't calculated the index on Actual vs Actual LY and;
2) All columns have the same width because we switched the Expressions and Dimesions.
We have tried to make a calculated Dimension but that doesn't seem to work for this case. Unless you know how to to this better ofcourse.
An example of the database is attached.
Does anyone understand the problem we face and know how to solve this without changing the data input form.
Thanks,
René Laan
