Flag x number of previous rows

[jdrob2014]

Looking to flag x number of previous rows based on an initial flag in a column. So the 8 weeks prior to the flagged 'is_christmas_week', the previous 9 weeks prior to 'is_halloween_week' etc.

Then as a bonus want to also flag from 'is_valentine_s_week' back to where the christmas weeks end, and easter back to when valentine's ends. So christmas and halloween have distinct 8 or 9 week periods every year, but valentine's and easter depend on when the flags in other columns end.

Attached is workbook with where i've gotten so far, and the desired output. I was trying a multi-row tool but struggling to get that to actually flag something in row-8.

[IraWatt · Accepted solution]

Hey @jdrob2014,

The key to this is the multirow formula it lets you check rows above and below the current row. This allows you to add a formula like this to add in your 1's:

I have attached the example for is_Valentine_s_Day_Week and is_Halloween_Week.

If you want to learn more about the Multi-Row-Formula Tool the community has some quick and easy videos on the topic here: https://community.alteryx.com/t5/Interactive-Lessons/Multi-Row-Formula/ta-p/82872

Any questions or issues please ask

Ira Watt
Technical Consultant
Watt@Bulien.com