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 |
Solved! Go to Solution.
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,
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.
kayers -
Thank you kayers! with a little tweaking this was able to provide the results I was looking for.
Thank you Thableaus
This was very helpful!