Minimum value through out a 12w period of time

luizaarauj

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!!

 

 

jackcplanning

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

luizaarauj

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 😕

rob_marshall

@luizaarauj ,

 

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

 

Here is my data:

 

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

 

 

For a result of:

 

Hope this helps,

 

Rob

 

rob_marshall

@luizaarauj 

 

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

 

 

 

Rob