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

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

SupplyForecastData.xlsx

Workflow

Tips and Tricks

Custom Tools

Date Time

Transformation

Help

Dynamic Processing

Preparation

Data Investigation

Accepted answers

TheMattLeonard

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!

All comments

TheMattLeonard

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

SupplyForecastData.xlsx

SupplyForecastDataPic.jpg

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

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

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)

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703