checking condition within a value range

Afrooz

Hi everyone

Since this is a very important part of my work and I haven't got a solution yet, I'll post it again.

I'd be very grateful if someone could help me with this

I'm pretty new to Alteryx and I am trying to check a condition for a range of values. the condition is:

If [field] = "1" then put the 14 previous rows in [new field] to "1"

or another way of saying this is:

If there is any 1 within the next 14 rows in [field] then put the current field in [new field] to 1 and so on.

Basically what i'm trying to do is to check the condition within the next 14 rows not every 14th row.

Would be great if anyone could help me to solve this and thank you in advance.

Requirements:

The data set is within a period e.g 31-03-2012 - 30-08-2013

Data recorded monthly

also it needs to be grouped by date and customer ID, each customer has number of different records within that period.

I'd really appreciate any help

Regards,

Afrooz

Data Investigation

Accepted answers

BrandonB

If I am understanding correctly it looks like this is a multi row formula solution like the example below:

field 1 example.png

This checks to see if Field 1 contains the number 1 in the following 14 rows and sets the value of New Field to 1 if so. Is this what you are looking for?

BrandonB

The Multi Row Formula is dynamic and applies to each row of data. In Row 1, the new field value looks at rows 1 to 14 of the Field 1 data. The new field value in row 2 looks at the Field 1 data for rows 2 to 15. You can also check the box to group by a certain field if you only want to consider rows of data in specific sets of groups.

All comments

BrandonB

If I am understanding correctly it looks like this is a multi row formula solution like the example below:

field 1 example.png

This checks to see if Field 1 contains the number 1 in the following 14 rows and sets the value of New Field to 1 if so. Is this what you are looking for?

fmvizcaino

Hi @Afrooz ,

I've done an example looking for the last 3 rows, but you can easily insert the previous 11 rows as correctly showed by @BrandonB.

The only thing I added was the use of group by in multi-row formula in order to work for multiple dates and IDs.

Let me know if that works for you.

Best,

Fernando Vizcaino

2020-01-13 checking condition within a value range.yxmd

Afrooz

Hi Brandon

Thanks for your solution. I think multi-row formula only checks the 14th row. But I just tried your solution and it basically copies every 1 in Field to the new field

Thanks

Afrooz

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703