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?

Accepted answers

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.

All comments

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

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.

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!

Quick Links

Can you help?

Unanswered questions

Workaround for Formula & Closing Balance summary methods together in Polaris
Hello folks, Does anyone know how to work around the Polaris limitation of using the Formula or Ratio summary method alongside the Closing Balance within the same line item? I have a Business Unit list where the total (All BUs) must display the Corporate (1 of 20 leaf items under All BUs total) number of Unique Customers.…
Anaplan Report page issue in the UX
Hello community, I have an issue with anaplan report (functionality). The issue I have is within couple of slides. The data in that report is scrolling into a new page. There is a hierarchy in the rows and line items and time in the columns. My problem is, the hierarchy is getting broken in the second page. One of the node…
ADO - S3 incremental load vs. full replace
I want to create an S3 connection that will have monthly data stored in individual csv files that is connected to ADO. Around month end, data will be uploaded multiple times, replacing existing csvs. When this happens I want to replace the existing data for that month with the new file. I don't want to have to create a…