Formula for calculating Time Coverage
Hi everyone! I'm having trouble in resolving these request in Anaplan. Basically I've got 2 line items in a module with Time Scale Weeks:
- Expected Sales Quantity = Qty of Product that is expected to sell in every week
- Final Stock Quantity = Qty of Product in Stock in every week
The request is to calculate for each week of the module, how many weeks that specific
quantity of Final Stock Quantity covers the Expected Sales Quantity (as the sum from the week of the final stock quantity onwards.)
Below an example:
I am not finding any formula within Anapedia that can solve the case.
Thank you so much for your help!
Joana
Best Answers
-
Hello @JoanaNdreu
Here's a solution that uses moving sum, which reduces anaplan's performance,
I use a technical list that increases anaplan's volume.
But I'm answering your question precisely.
Other solutions exist and can be satisfactory without being precise.
0 -
it's works
This idea works,
Unfortunately, there are IFs and it's not dynamic, because the depth of coverage will depend on the formula.
On the other hand, there may be fewer Movingsum calculated, depending on your sparsity in the module.what do you prefere ?
0
Answers
-
Hi @axel.frot, thank you very much for your reply! Is there also any solution without using a custom list?
0 -
Hello @JoanaNdreu
What's wrong with using a technical list?
You can split this method over as many line items as you like.Or how about a calculation based on "average coverage" over n periods, for example?
0 -
Hi @axel.frot thank you very much for both the solutions you suggested here! They were very helpful and yes, I have tried both solutions and I agree with all benefits and critic points you mentioned. The formula one is more simplier but statis. I decided to go with the custom list of week nbr since I can have situations where the movingsum should sum through more that 4 periods (also 11, 12 and more..) even if it takes more space in the workspace.
Thank you again!
Joana
0 -
Hi Joana,
If you don't mind please mark the thread as Answered. You will get the option against any of axel.frot's replies
0 -
Did it @anirudh , thank you!
1 -
the solution given by @axel.frot is perfect, maybe even better in the Polaris engine where sparsity and size are better managed.
I just want to offer an alternative. This alternative takes a heavier toll on the calculation performance but the trade off is that consumes less space.
One could argue that goes against Anaplan's best practices (Break up formulas to leverage Anaplan's parallel computing power) but, then again, modules where stock cover calculation is needed are usually modules that use the SKUs, Time and Locations dimensions, so adding a 4th dimension that will increase the size of that module x5 or more may be worth considering an alternative?
Finally, the Periods of Cover metric is only relevant up to a given number (depending on the industry and lead time) so after, lets say 4 months, we only care that we have +4 month's worth of stock. So we can use the following formula in our "Cover" line item:
IF 'Closing Stock ' <= LEAD('Sales (S)', 1, 0) THEN 'Closing Stock OH' / LEAD('Sales (S)', 1, 0) ELSE IF 'Closing Stock OH' <= MOVINGSUM('Sales (S)', 1, 2) THEN 1 +('Closing Stock OH' / MOVINGSUM('Sales (S)', 1, 2)) ELSE IF 'Closing Stock OH' <= MOVINGSUM('Sales (S)', 1, 3) THEN 2 + ('Closing Stock OH' / MOVINGSUM('Sales (S)', 1, 3)) ELSE IF 'Closing Stock OH' <= MOVINGSUM('Sales (S)', 1, 4) THEN 3 + ('Closing Stock OH' / MOVINGSUM('Sales (S)', 1, 4)) ELSE 41
