rule-based Total calculation in P&L

Ellina Mnikh

Hi all,

I want to get a total column based on rule calculations (f.e. sum or avg)

My Layout:

Cube a - hide. The Cube contains the actuals. On the cube is applied a rule to calculate some positions of P&L.
Cube b - hide. The Cube containg the plan values. 
Cube c - hide. Actuals total by month.
Algorithm d - if(c<>0,a,b) --> Displays plan values for the future month and actuals for the completed month.

The idea was to add a new cube "Total" with P&L and Year as Dimensions and use the function detail by "Year". 
On this cube I would apply the rule "Total_PL".

However I have a problem with defining the rules:
f.e. Total of OPEX is a sum of month-based OPEX values. For the year 2019 I could take via data picker the values from the cube P&L. 
In 2020 I need to take the sum of values in P&L (Jan-Sep) and add the sum of values in PL Plan (Oct-Dec). I'm not sure, if it's possible with data picker.

Do you have any idea how to solve it?

Thanks!

Best Regards,

Ellina

------------------------------
Ellina Mnikh
Controller
Germany
------------------------------

Etienne CAUSSE

Hi,
I don't think you can manage that with Data Picker directly.
I would advise, when you load the Actuals value, to add a step where you feed a new cube "Updated P&L projection" with the Actual Months + remaining planned months, and use this in your layout. In the dataflow you will have the required granularity to build the proper totals and it will simplify your front-end a lot.
Cheers,
Etienne

------------------------------
Etienne CAUSSE
Finance Project Manager
France
------------------------------
-------------------------------------------