This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
In this case, I believe the Multi-Row tool is going to help you resolve.
If you filter your data by Order Number, Material, and Week, you can then group by Order Number and/or Material in the Multi-Row tool and check to see the prior week's value. For example, to find out if an order number existed in a prior week, I would configure the tool as follows:
1. Create New Field "Order Previous Week"
2. Set the Values for Rows that don't exist to "NULL"
3. Group by Order Number
4. Expression = [Row-1:Order Number]
This will pick up the prior row's order number, grouping by each order. If there is no prior week for that order number (because it's the first week) it will show NULL, indicating a new order that week. With a series of these multi-row formulas, you can check all your required fields for existing values and/or changes from prior week. You can also look ahead to the next week as well for the case where you want to identify a Deleted order - if the snapshot date isn't equal to the current week and the Row+1 Order Number is NULL because it does not exist, then you know that order was deleted in the current week.
I've attached a workflow that should get you what you need, but I would recommend trying to configure the Multi-Row tools yourself as well - this is one of the more powerful/dynamic tools since it allows you to work with data not just in the current record but in n number of rows before/after the current one!