Week to Week Comparision Using Multi-Row Tool

[laurennewton]

Hi community! I've attached a sample workflow of where I am currently stuck...

This is what I am trying to achieve, currently I have a large historical database that has several records of data grouped by each week/iteration, with a total of 99 weeks/iterations (i have them named iteration number but they signify week). Week 0 signifies the oldest historic week and week 99 is the most recent past week.

I am trying to compare week 0 to week 1 and if there is a unique ID in week 1 that did not exist in week 0 I flag that week with an X. This logic I have done already in the multi-row formula. Here is where I am stuck: I need the workflow to then move on and compare week 1 to week 2 and repeat this flagging methodology. My issue is how can I get the multi-row tool to know to switch to week 2 vs wk 1. Is this possible? 

Thank you!

[JamesCharnley]

Hi @laurennewton 

I think the XML is corrupted on your attached macro so it can't be opened to take a look. Can you check that and reupload a version please and it'll be easier for somebody to help you out

[laurennewton]

@JamesCharnley thanks for catching that! i've uploaded a new example

[JamesCharnley]

Hi @laurennewton 

Sorry for the delayed response, the file still seems corrupted so I've tried to build something from scratch that I think achieves what you want.

I think an issue with using the Multi-row here is that you've said you have multiple records grouped by week, so it'll be difficult to dynamically look back a varying number of rows to a different group of records to compare records in a long if statement.

I've instead attached a quick iterative macro proof of concept that is essentially isolating two given weeks using the engine iteration number, joining the records by their IDs so I can tag the J output as 'X', and then bringing all of the records back together so we can iterate

Hope that helps.