Dimension and/or Expression calculated

Mon, 05 Nov 2012 12:03:55 GMT

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

Re: Dimension and/or Expression calculated

Mon, 05 Nov 2012 15:24:23 GMT

I don't get what you need. Try to be more clear and attach some qvw file to help understand what do you need.

Best regards,
TMF

topic Dimension and/or Expression calculated in QlikView

Dimension and/or Expression calculated

Re: Dimension and/or Expression calculated