How to detect with 1st date with condition

Hello, I'm tring to detect 1st date by per item(ex parts) with some condition. ex)

Parts Inventory module      
(appling parts list, time)      
Parts Lineitem 1-Aug 2-Aug 3-Aug 4-Aug
A Inventory 10 10 -10 20
B Inventory -20 -20 -20 0
C Inventory 10 20 30 -10

I want detect 1st negative date by each item, desireble result is as follows

1st shortage date module(appling parts list, time is not applicable)
Parts Shortage date
A 3-Aug
B 1-Aug
C 4-Aug

calendar type is weeks: general is the any way to do this?

Best Answer

  • Start Date should be dimensionless; i.e. its Applies To should be blank and Versions and its Time Scale should be Not Applicable. And its formula should be
    That's where you've gone wrong I think.


  • Hi Ryohei

    You can use this function to detect the same:

  • Using MIN won't work because the minimum may not be negative. Also, applying MIN to the Inventory line item will return the value of the inventory, not the date.

    Here's how I'd do it: Add a line item called Shortage Date (date format) to the Parts Inventory module, with the formula
    IF Inventory < 0 THEN START() ELSE BLANK
    Give this line item the same dimensionality as Inventory, including time, and set its summary function to None, Time: First non-blank. (The most important thing is for the sum over time to be first non-blank; it doesn't really matter about the other sum.)

    Now, in your First Shortage Date module, you can have a line item Shortage Date (also date format), which doesn't apply to time, with the formula
    Parts Inventory.Shortage Date[SELECT: Time.All Periods]
    This should give you the first date with a negative inventory, for each item in the Parts list, which I think is what you want.
  • Hello Peter,

    Thank you for your solution.
    Yes! your solution is what i want to realize.
    But there is a problem. i'm using weeks : general in calendar settings.
    in this case, All period syntax in [SELECT: TIME.All Periods] can not be used.....  

    Ryohei Sasakawa
  • Hello Harish,
    thanks for your suggestion.
    i'll try it.

  • In that case, use this modified formula for Parts Inventory.Shortage Date:
    IF Inventory < 0 THEN START() ELSE NEXT(Shortage Date)

    And this modified formula for First Shortage Date.Shortage Date:
    Parts Inventory.Shortage Date[LOOKUP: Start Date]

    where Start Date is a date-formatted line item that returns the start date of your timescale. That should work.
  • Hello Peter,

    Would you tell me more detail about Start Date?

    Parts Inventory.Shortage Date

    IF Inventory < 0 THEN START() ELSE NEXT(Shortage Date)

    I did this.

    and this modified formula for First Shortage Date.Shortage Date:

    Parts Inventory.Shortage Date[LOOKUP: Start Date]

    I added 'Start Date' line item in First Shotage Date Module with date format (this line item time scale is 'day')
    and i  set shortage date line item formula using  lookup functoin you menthioned .(this line item  time scale is 'not applicable')
    Formula could be set but the result is Blank

    Is the any required settings? or do i understand wrong?

    I need your more help.
    (i attached my line item setting and module download data xls) 


  • Hello Peter,

    I could solve what i want to do by your proposal.
    Thank you so much!!

    i'm still wonder why lookup with start date(current period start) pick 1st date.... 

    Best regards,
    Ryohei Sasakawa
  • It works because it's a recursive formula: defining Shortage Date in terms of NEXT(Shortage Date) makes it work backwards, noting each time it gets a negative Inventory. Each time it notes a date, the date gets earlier & earlier, so that at the end of the process (i.e. the start of the timescale) it's got the earliest date with a negative inventory. Step into it with F8 and you'll see what I mean.

    Glad I could help!
  • oh! i could undetstand how it works!
    It's wonderfull idea!

    Thank you very much again!