Split a Yearly amount in Months according to Start Date and End Date
Dear All,
'Hope you're fine!
I'm looking for splitting a full year amount between months according to a Start Date and End Date.
For now, I identified 4 cubes: see below.
All cubes have the 3 dimensions: Scenario / Company / Product
- Full Year amount (single: at Year level)
- Start Date (Date: at Year level)
- End Date (Date: at Year level)
- Monthly Split (Single: at Month level)
The split must be done in proportion on number of days by months.
From March, 15th 2024 to April, 30th 2024: 46 days splitted in 16 days on March and 30 days in April; thus 460 would be allocated to 160 in March and 300 in April.
Dis you already set this? How? What would be your advices?
Ideally, I'ld rather not use Nexel…
Accepted Answers
-
Dear @BENOIT ROLLAND EYROLLES ,
More than happy to help ,
Procedure Step 1 : you need to create a Day based Temporary cube ( A ) that contains all the entities on which the calculation must be done. The value 1 must be set every where you are between the Start Date and the End Date.
Procedure Step 2 : You need to put the content of the cube A on a Monthly Based Temporary cube B ( at this step you have the number of active days per Month )
Procedure Step 3 : You need to put the result of A also on a Yearly based cube C ( at this stage you have the number of active days per Year )
Procedure Step 4 : The result is you Monthly Destination cube where you have the DF : Result = Full Year amount*B/C
Best Regards,
Fethi Zerara
0 -
0
Answers
-
Since Board inherently performs a sum, I recommend performing the calculation on a daily basis.
This approach ensures that when aggregating into a monthly cube, the correct number of days is already assigned.
Ideally the process to calculate the number of days within two dates should be like the following:
where in yellow is the date converted in number.
Admin Task: Create another date cube type, it must be populated for all the days (ideally for the entire time range of the database):
And then convert this date cube to a numeric one (like shown above for start&end date).
In the last column is the number of days in scope.
With this information you can then move on with the monthly allocation.
Hope this helps.
Regards,
Tommaso
0 -
Dear @Tommaso Riva , @Fethi ZERARA
Thanks for your feedback.
I can't create the new Physical Date cube (@day level) suggested as the application is not at day level; only Month.I better understand the way to proceed and I'll test on month split at first.
Any other thoughts?
0 -
In this case you can take use of the Function : DATEDIF (start_date, end_date,"d") which gives you the diffirence between to dates.
Example :
Regards,
Fethi Zerara
0
Categories
- All Categories
- 2K Forums
- 1.8K Platform
- 173 Academy
- 332 Resources
- 1 Board Knowledge Base
- 50 Best Practices
- 49 How-To Guides
- 19 Board Advocacy Program
- 197 Blog
- 4 Groups Hub
- 4 About Groups
- New Community Members
- Board Community Captains
- DACH
- Japan
- 5 Community Captains
- 1 About Community Captains
- 3 Meet the Community Captains
- 1 Topics & Thought Starters
- Learn from the Board Captains
- Release Notes
- Academy
- 2 Board Academy
- 8 ILT/VILT Course Catalogue
- 13 e-Learning Course Catalogue
- 4 Academy Forum
- 1.3K Idea Exchange
- 341 Partner Hub
- 94 Support
- 14 FAQ's
- Customer Support Portal
- 54 Support Articles
- BEAP