Minimum value through out a 12w period of time

Highlighted
Not applicable

Minimum value through out a 12w period of time

Hi! Hope that you all are doing fine with this crisis! 

 

I have a line item that, for each reference, calculates the balance between need and stock for each week.
I would like to create another line item, without the time dimension, that gives me the worst situation in the next 12 wks for each reference and (another line item) at which week this is going to happen. 

 

For example:

- for the first reference, it would be -2322 pcs, at wk 5/10/20 (or in 7wks)

- for the 2nd reference, it would be -133 pcs, at wk 26/10/20 (or in 10 wks)

 

I already tried with rank and min functions but I couldn't find the right solution. Can anybody help me, please? 🙂

Thank you very much!!

 

luizaarauj_0-1598363756702.png

 

4 REPLIES 4
Highlighted
Certified Master Anaplanner

Re: Minimum value through out a 12w period of time

Hi,
I believe the rank function does not rank against the time dimension. You may need to create a faux time dimension to get around this - but definitely not ideal in terms of long term management.
Best,
Jack
Highlighted
Not applicable

Re: Minimum value through out a 12w period of time

Hi Jack!
Thank you for your reply!
Yes, I have think of that for rank and that's why it wouldn't work, as every week the scale changes..
So even if we build a process and everything, I think it is a lot to do just to get some ranking and do not have the final answer that I need in the end 😕
Highlighted
Master Anaplanner/Community Boss

Re: Minimum value through out a 12w period of time

@luizaarauj ,

 

I think I have a solution for you, but please take a look to make sure.

 

Here is my data:

2020-08-25_11-08-13.png

 

Create a line item using MovingSum to figure out the lowest number in a rolling 12 week span:

MOVINGSUM('Line Item 1', 0, 12, MIN)

 

Create another line item getting the date if the number in Line item 1 equals the MovingSum Number.

 

Then another line item using MovingSum to return the FirstNonBlank of the date

 

2020-08-25_11-11-39.png

 

For a result of:

 

2020-08-25_11-07-02.png

Hope this helps,

 

Rob

 

Highlighted
Master Anaplanner/Community Boss

Re: Minimum value through out a 12w period of time

@luizaarauj 

 

If you need the final answer in period format, you last line item would be line the following:

 

2020-08-25_11-18-43.png

 

2020-08-25_11-19-16.png

 

Rob