How to identify first row where forecast becomes negative for every 15 weeks

[vibes360]

I have my dataset in the structure below and I would like to identify the first row where my Forecast value goes negative for every 15 weeks of a Tile_Num. The Tile Number is based on the Component and Snapshot Week. I have a column called Data Check that identifies the Week Number for every time the Forecast goes negative, however I only want to identify the first time the Forecast goes negative for every Tile_Number which I have shown in the Proposed Data Check Column. The data needs to be analysed for every 15 weeks of a component and it's snapshot week

I would appreciate if anyone can show me how I can get the Proposed Data Check Column using the Alteryx Tools

I have attached a copy of my data to help understand what I am explaining

[TheMattLeonard · Accepted solution]

I believe you're after the Multi-Row Formula tool. It has an option to "Group by" other fields which will help identify the first time your value is negative per the Tile Number.

Starting the configuration panel from the top of the Multi-Row formula tool, use the follow:

Create New Field

Name the new Field "Proposed Data Check". Type: VWString

Num Rows: 1 . Values for Rows that don't Exist: 0 or empty

Group By: Select Tile_Number

Expression:

IF [Running total] < 0 AND [Row-1:Running Total] >0 THEN [DataCheck] Else Null() ENDIF

Hope this helps!

[vibes360]

Please can you update the formula to capture cases where there is no issue for the next 15 weeks so that the Proposed Data Check field shows 'No issue' for al those weeks

I have attached sample data and a pic of the data as well

[aatalai]

@vibes360 this identifies the the first week per tile that becomes negative, you might need to use a a find and replace if you want it joined to the original data set

[vibes360]

Thank you. This could also work

[aatalai]

@vibes360 if it works can you mark it as a solution (you can mark more than one solution)