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

 

Tagged:

Answers

  • 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
  • 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 😕
  • @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

     

  • @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