Distributing total value on 12 months ahead based on a month

Khaleelah

Hi Guys

I have a requirement to divide a total across 12 months based on two months during the year

namely April and October

Example:

April 2023 - 12000 USD will be split from April 2023 to march 2024.

October 2023 - 2000 USD will be split from Oct 2023 to Sep 2024

I have come across the below ticket which is similar to my requirement but im not sure to have all the answers

ANY IDEA pls

Distributing total value on 12 months ahead — Board Community

i have added a comment to the post

QUOTE

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.

ENDQUOTE

Thanks

Lucas Charello

Hello Khaleelah,

There are many ways you can do this. I worked on a little one on my side and here is the Screenshot of my procedure.

There are a few points of attention to make this work. Here in my example, all cubes are Temporary cubes, which will avoid you creating many cubes that will only serve you for a feel back-end calculations. If you're not familiar with those, you should definitely take a look at the article I published lately, here. Also, all those cubes only have the dimension "Month" or "Mois" in French. Depending on your case, you will certainly want to add other dimensions, like "Project", "Customer" or whatever. I'll not treat this part of the answer in the following comments, but feel free to ask me more questions about it if you can't solve the problem.

Khaleelah

Hi Lucas

Thanks.

Clarification below

Remove tree ? why

is 12 months a key word ? provide the layout please

can you please explain the last dataflow (provide the layout) before refresh screen

Thanking you in advance

Lucas Charello

First, you have to find a way to put a "1" in the Month corresponding to the first month of your spread of a cube. This is generally not a problem as you probably have a screen with a selection or a data entry. Here, I forced the "1" using a hard selection and a dataflow in a base setup from steps 2 to 4.

Then, here is where you start : Step 5. You send your "1" twelve months in the future using the Time Function > Period offset = -12. These period offset are tricky, I never remember if I should use + or - 12. Test it a lot !

After, you will use the Time function > Last Value coupled with an If statement to fill with "1"s the months you want to fill with your values:

Then you divide that cube by 12 so you have 1/12 on the cell of every month you want to spread your values. Lastly, you multiply that cube by the one you have that contains the value you want to spread in twelve months.

You can try to do this with the Temporary cubes first and once you get to point you used them on your project. Remember to test it A LOT in the dev instance before going to put this available to the customer! Result :

Be careful, if the cube containing the value has a time dimension, this may fail. An idea would be to do some extra data manipulation with temporary cubes to remove that dimension.

You will have to find which cubes are interesting to have as Temporary or "Normal" cubes. Be careful with selections on the procedure to avoid clearing data from normal cubes when you don't want to. Ex: If you're calculating the value spread for one project, and you don't won't other projects to be modified, put a selection on your project dimension.

Note: Be even more careful with the Last Value function ,specially if you are using a "Normal" cube to calculate things. This function will find any existing last value, even if it's out of the procedure selection. This can be very dangerous! I would advise you to use Temporary cubes for the last values if possible.

Hope this helps!

Have a great day.

Lucas

Khaleelah

Thank Lucas for the detailed explanation. Will try