TM1 Forum

Posted: **Thu Sep 27, 2012 5:04 pm**

Hi,

I'm wondering if anybody has a design idea on how to solve this Sales Analysis (Bridge Analysis) problem in TM1?

Please find attached an Excel file showing detailed calculation requirements.

Based on previous period quantity and revenue as well as current period quantity and revenue we need to calculate 3 values to describe the change in revenue from previous period to the current period. This "bridge" describing the revenue change is described by the three parameters: Price / Volume / Mix (calculations are shown in the attachment).

The cube will contain a number of dimensions (e.g. Customer hierarchy incl. customers (>100.000), item hierarchy incl item (>50000), Date (month/qtr/year), country, etc.). And the bridge calculations will need to work on all levels on all dimensions.

I have tried to describe the calculations/requirements in words but I always end up deleting the text as it gets confusing - I hope having a look at the attached file will explain the problem?

I hope this makes sense

And if not then please let me know and I will try to explain further.

Thank you in advance...

Posted: **Thu Sep 27, 2012 5:31 pm**

Several things:

Create a measure dimension that has, at a minimum: Rev, Qty, Price, Price_Var, Vol_Var, Mix.
Add an attribute to your Time dimension called "Prev_Period" and populate it with the element name representing the corresponding previous period you want to do the variance calc on.
Add the following rules:

Code: Select all

['Price_Var'] = ['Rev'] - DB(YourCubeName,Dim1,Dim2,Dimx...,Attrs(TimeDimension,!TimeDimension,'Prev_Period','Rev')) * ['Qty'];
['Vol_Var'] = ['Qty'] - DB(YourCubeName,Dim1,Dim2,Dimx...,Attrs(TimeDimension,!TimeDimension,'Prev_Period','Qty')) * DB(YourCubeName,Dim1,Dim2,Dimx...,Attrs(TimeDimension,!TimeDimension,'Price'));
['Mix'] = ['Rev'] - (DB(YourCubeName,Dim1,Dim2,Dimx...,Attrs(TimeDimension,!TimeDimension,'Prev_Period','Rev')) + ['Price_Var'] + ['Vol_Var']);

Posted: **Thu Sep 27, 2012 5:52 pm**

Hi tomok,

Thank you for your prompt reply.

I agree we should have measures as you describe and that we could use ATTRS to find the values for the previous period (revenue and qty). We would also need measures to contain "Price Revenue" and "Volume Revenue".

But please have a look at the calculations on the aggregated levels.

In the example, data for January and February is shown. Data is shown for individual "customers" and it is aggregated to "customer group" level (the rows). These calculations are interesting

And on top of that there's a 3rd example showing Qty February (the two previously mentioned months aggregated) - and now the calculations are based on the "current level in the current dimension". As I see it it's not possible to solve this using standard N: and C: rules or consolidation ?

Thanks,

TM1 Forum

Sales Analysis

Sales Analysis

Re: Sales Analysis

Re: Sales Analysis