Multirow formula - count consecutive days oos.

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

Workflow

Accepted answers

kelsey_kincaid

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.

ConsecutiveDaysOutofStock-Example.yxmd

All comments

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