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

Accepted answers

axel.frot

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.

axel.frot

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 ?

All comments

axel.frot

Hello @JoanaNdreu

JoanaNdreu

Hi @axel.frot, thank you very much for your reply! Is there also any solution without using a custom list?

JoanaNdreu

@axel.frot

axel.frot

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?

axel.frot

it's works

what do you prefere ?

JoanaNdreu

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

anirudh

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

JoanaNdreu

Did it @anirudh , thank you!

AlejandroGomez

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 4

Quick Links

Can you help?

Unanswered questions

ANAPLAN XL View
Hi, I need help in pulling a specific view in Anaplan XL. The first grid should display attributes of various projects, while the second grid should present the monthly financial data for these projects. Thanks in advance.
Add list members to a selective access list
Hi, I am working on a solution where the User should only have access to write/edit the list members that they add from an Action on the frontend. This action adds another list member to a selective access list. The problem is, in order to run the action, you need access to the entire list, so you would see all list…
Dimensionality Issue
Hi ALL, In my model, I have Need stored at a 'D3' RS Group level. I need to use this Need in another module at the G5 Location level. I currently have a mapping module where each G5 Location maps to a D3 RS Group. This Module uses D2 Role as dimension. but the line item I've to find the RS Group (Find D3 RS Group = 'FIND…