rule-based Total calculation in P&L

Options
Ellina Mnikh
Ellina Mnikh Customer
First Comment First Anniversary
edited October 2020 in Platform
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.


image
image


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
------------------------------
Tagged:

Answers

  • Etienne CAUSSE
    Etienne CAUSSE Customer
    First Anniversary Advocate Community Voices Name Dropper
    edited October 2020
    Options
    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
    ------------------------------
    -------------------------------------------

Categories