How to detect with 1st date with condition

sasakawar

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?

peter_mcanena

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

CURRENTPERIODSTART()

That's where you've gone wrong I think.

harish_bk

Hi Ryohei

You can use this function to detect the same:

x[MIN:y]

peter_mcanena

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.

sasakawar

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

sasakawar

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

Ryohei

peter_mcanena

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.

sasakawar

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:

1
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) 

thanks

ryohe

sasakawar

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

peter_mcanena

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!

sasakawar

oh! i could undetstand how it works!
It's wonderfull idea!

Thank you very much again!