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  

[BrandonB · Accepted solution]

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

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

[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

[BrandonB · Accepted solution]

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. 

[BrandonB]

example

 

[Afrooz]

Thanks Brandon. 

your example seems like what i need but when I groupby it by customer ID and date it doesn't give me this

not sure what's wrong

[BrandonB]

You definitely want to group it by customer ID but your issue is probably when you group it by date because you have a range of dates. You likely need a formula before this so that you can say if a date is between a range, set it to a value. If it is in the next range, set it to that next value, etc. Otherwise, it is treating each date as its own group and cuts off the calculation. 

[Afrooz]

Hi @fmvizcaino

thanks for your response, I Brandon's example is what I need but it doesn't work when I group by it by customer ID and Date. It just copies the Field in new field

Thanks

Afrooz

[Afrooz]

Thank you so much @BrandonB 

It works perfectly fine. I was stuck here for days. 

thanks a lot for your help

Afrooz