if statement based on multirow

unknown

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

danrh

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.

solution.yxmd