Multirow formula - count consecutive days oos.

[aelam]

I'm trying to create a field that will count the consecutive day on hands are out of stock.  I believe i can accomplish this with the multi - tool formula, but i'm receiving errors in the tool with attempting. 

Below is an example of my data.  Item_location 6462806464 i would expect a count of 3 and item_location 9724996471 i would expect a count of 7.

date	item_location	oh qty
6/17/2019	6462806464	0
6/18/2019	6462806464	0
6/19/2019	6462806464	0
6/20/2019	6462806464	5
6/17/2019	9724996471	0
6/18/2019	9724996471	0
6/19/2019	9724996471	0
6/20/2019	9724996471	0
6/21/2019	9724996471	0
6/22/2019	9724996471	0
6/23/2019	9724996471	0
6/24/2019	9724996471	8

[Thableaus]

Hi @aelam 

Here's what I came up with:

- Convert your date field to date format of Alteryx

- Use Multi-Row formula to flag consecutive days out of stock

IF (ToDate(DateTimeAdd([Row-1:Parsed_Date], 1, "days")) = [Parsed_Date] OR ToDate(DateTimeAdd([Row+1:Parsed_Date], -1, "days")) = [Parsed_Date])
AND [oh qty] = 0 THEN 1
ELSE 0 ENDIF

- Summarize the number of Flags of Consecutive Days out of Stock

- Join by item_location to bring this number to your dataset

WF attached, if you need more support let me know.

Cheers,

[kelsey_kincaid · Accepted solution]

I used a multi-row formula to simply count the rows, grouped by item_location. Then I used a formula tool to reduce that number by 1 since we don't want to include the first day as a whole day out of stock. To get the number to be the same for every line instead of having a running count, you would just need to summarize, find the max and re-join with your original data set just like @Thableaus did.

[aelam]

kayers - 

Thank you kayers!  with a little tweaking this was able to provide the results I was looking for. 

[aelam]

Thank you Thableaus 

This was very helpful!