Distributing total value on 12 months ahead

Guillermo Abascal
Guillermo Abascal Active Partner
Third Anniversary 10 Comments Level 100: Foundations of Building in Board
edited July 2020 in Platform
Dear Community

I would like to know a way to distribute proportionally a total value on 12 months, starting from an specific date. I have a cube with the dimension "Project", that has the total benefits for each project, and I have to distribute that value on 12 months, the thing is that, each project has different dates, so the distribution has to be carried on depending on the date of each project. 

I have a second cube with dimension "Project" and "month" that stores with 1 and 0, the specific date from which the distribution has to be done.


image


As you can see from the image, the first column is the projects codes, the second one would be my first cube, total benefit, and the second, the starting date for the distribution.

The final outcome should be something like this:


image



Thank you for any help

Regards

------------------------------
Guillermo Abascal

------------------------------

Answers

  • Ravichandra Aj
    Ravichandra Aj Active Partner
    10 Comments Second Anniversary Photogenic
    edited September 17

    @Guillermo Abascal

    : Yes you can achieve this .

    you should have a reference cube from which month you want to distribute . apply the following function(attached screenshot) and flow it in target cube.
    if you get any doubt feel free to ask.

    Thank You,
    Ravi

    @Ravichandra Aj cannot see the screenshot

    ------------------------------
    Ravichandra Aj
    Associate Software Enginner
    SIMBUS TECHNOLOGIES PRIVATE LIMITED
    India
    ------------------------------
    -------------------------------------------

  • Etienne CAUSSE
    Etienne CAUSSE Customer
    Fourth Anniversary 250 Up Votes 100 Comments 100 Likes
    edited July 2020
    Hi @Guillermo Abascal,
    I'm not sure how @Ravichandra Aj's solution works, on my side I would try the following:
    - add a third cube "monthly ratio" based on second cube with the dates, using the function "yearly moving average" on that cube and flow it to the third cube. Given your current structure, the yearly moving average should be 1/12 for each of the months following the beginning month, 0 before and 0 after 12 months.
    - then calculate your result with simply a dataflow : cube "total benefits" * cube "monthly ratio" = expected result.


    Etienne

    ------------------------------
    Etienne CAUSSE
    Finance Project Manager
    France
    ------------------------------
    -------------------------------------------
  • Guillermo Abascal
    Guillermo Abascal Active Partner
    Third Anniversary 10 Comments Level 100: Foundations of Building in Board
    edited July 2020
    Thank you for your answers!

    @Ravichandra Aj I was not able to fully understand your proposal, however thanks for the advice.

    I'll go for @Etienne CAUSSE idea, since it seems to be very easy to implement.


    Regards




    ------------------------------
    Guillermo Abascal
    Spain
    ------------------------------
    -------------------------------------------
  • Ravichandra Aj
    Ravichandra Aj Active Partner
    10 Comments Second Anniversary Photogenic
    edited July 2020
    1. you have a source cube where you have whole fiscal year data.
    2. you need to have intermediate cube in which you are setting month start period (i.e where you are flowing one ) 
    3. @Guillermo Abascal as suggested by @Etienne CAUSSE run the third step



    ------------------------------
    Ravichandra Aj

    India
    ------------------------------
    -------------------------------------------
  • Khaleelah
    Khaleelah Customer
    10 Comments Name Dropper Level 200: Building A Planning Solution in Board

    hello Guys.

    Im new to board and i have same requirement. Can someone pls give an example of the procedure.

    @Ravichandra Aj i cannot understand your point since i cannot see the snapshot you are referring to.

    @Etienne CAUSSE does the function "yearly moving average"  exist on board 14 or is it a userdefined function/procedure.

    Thanking you in advance.

Categories