if statement based on multirow

[Inactive User]

Hi,

I need to create flag based on last 7 days for a given ID and date. Appreciate if you could upload workflow here. 

Also similar to this, I have to calculate separate  flags for 

last 14 days 

next 7 days and next 14 days 

Input

ID	Date	Date 2	Code
abc	1/1/2014
abc	1/2/2014
abc	1/3/2014
abc	1/4/2014	1/4/2014	A
abc	1/5/2014
abc	1/6/2014	1/19/2014	A
abc	1/7/2014
abc	1/8/2014
abc	1/9/2014
abc	1/10/2014
abc	1/11/2014
abc	1/12/2014
abc	1/13/2014
abc	1/14/2014
abc	1/15/2014
abc	1/16/2014
abc	1/17/2014	1/19/2014	A

 Output

ID	Date	Date 2	Code	Flag
abc	1/1/2014			A not present in last 7 days
abc	1/2/2014			A not present in last 7 days
abc	1/3/2014			A not present in last 7 days
abc	1/4/2014	1/4/2014	A	A present in last 7 days
abc	1/5/2014			A present in last 7 days
abc	1/6/2014	1/19/2014	A	A present in last 7 days
abc	1/7/2014			A present in last 7 days
abc	1/8/2014			A present in last 7 days
abc	1/9/2014			A present in last 7 days
abc	1/10/2014			A present in last 7 days
abc	1/11/2014			A present in last 7 days
abc	1/12/2014			A present in last 7 days
abc	1/13/2014			A present in last 7 days
abc	1/14/2014			A not present in last 7 days
abc	1/15/2014			A not present in last 7 days
abc	1/16/2014			A not present in last 7 days
abc	1/17/2014	1/19/2014	A	A present in last 7 days

[BenMoss]

The solution provided in this post should be applicable to your problem.

Give it a try and let us know if you are successful.

If you need any help interpreting it please throw us a question...

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Multi-Row-Formula-to-Count-Days-betwee...

Ben

[danrh · Accepted solution]

You could do this all in one Multi-Row Formula tool, but it would be kind of messy --- lots of OR statements, and changing it from 7 to 14 days would be a little bit of a job.  I'd suggest breaking it into two tools to make it simpler to build and maintain:

The Multi-Row Formula simply tracks the last time there was an "A" in the Code column, and then the Formula tool compares each record's date with the last occurrence date.  Changing from 7 to 14 just requires a couple minor changes in the Formula tool.