Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Multirow formula - count consecutive days oos.

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

 

4 REPLIES 4
Thableaus
17 - Castor
17 - Castor

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,

kelsey_kincaid
12 - Quasar

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

aelam
7 - Meteor

kayers - 

 

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

aelam
7 - Meteor

Thank you Thableaus 

Labels