community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Multirow formula - count consecutive days oos.

Meteor

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.

 

dateitem_locationoh qty
6/17/201964628064640
6/18/201964628064640
6/19/201964628064640
6/20/201964628064645
6/17/201997249964710
6/18/201997249964710
6/19/201997249964710
6/20/201997249964710
6/21/201997249964710
6/22/201997249964710
6/23/201997249964710
6/24/201997249964718

 

Alteryx Certified Partner
Alteryx Certified Partner

Hi @aelam 

 

Here's what I came up with:

 

SolSimple.PNG

 

- 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,

Fireball

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.

OutOfStock.PNGOutOfStock2.PNG

Meteor

kayers - 

 

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

Meteor

Thank you Thableaus 

Labels