Formula for calculating Time Coverage

Options
JoanaNdreu
JoanaNdreu
in Modeling

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:

  1. Expected Sales Quantity = Qty of Product that is expected to sell in every week
  2. 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:

image.png

I am not finding any formula within Anapedia that can solve the case.

Thank you so much for your help!

Joana

Tagged:

Best Answers

  • axel.frot
    axel.frot
    Answer ✓
    Options

    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.

    image.png
    image.png
    image.png
    image.png
    image.png
    image.png
    image.png

  • axel.frot
    axel.frot
    Answer ✓
    Options

    it's works

    image.png

    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 ?

Answers

  • JoanaNdreu
    JoanaNdreu
    edited July 2023
    Options

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

  • JoanaNdreu
    JoanaNdreu
    Options

    @axel.frot

  • axel.frot
    axel.frot
    Options

    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?

  • JoanaNdreu
    JoanaNdreu
    Options

    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
    anirudh
    Options

    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
    JoanaNdreu
    Options

    Did it @anirudh , thank you!

Categories