Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Compare current week order snapshot to previous weeks


Hi all,


i am new to alteryx and i need your help to fix this problem


i need to compare the current week data(snapshot) to previous week and Check where there are any change to the existing order below is the same data.


per below data order 1234 present in week 23 but not in current week i need status a deleted order in remarks


Comparison rules


1. Compare current week line item with all other week

2.if order is new current week show status as new

3.if order exists in old week but does not exists in new week then status as deleted

4.track change in delivery date.

5.track change in quantity


i have provided how output should be but i am OK with better solution.


many thanks in advance for your help




MaterialMaterial TypeOrder NumberSupplierDelivery DateDelivery Quantitysnapshot DateWeek
1Raw1234ABC2018-07-02156/6/2018 12:2923
2Finished1235ABC2018-07-02116/6/2018 12:2923
3Finished1236ABC2018-07-02206/6/2018 12:2923
4Finished1237ABC2018-07-02136/6/2018 12:2923
1Raw1234ABC2018-07-02106/12/2018 12:2924
2Finished1235ABC2018-07-02116/12/2018 12:2924
3Finished1236ABC2018-07-02126/12/2018 12:2924
4Finished1237ABC2018-07-02136/12/2018 12:2924
5Finished1238ABC2018-07-02146/12/2018 12:2924
6Finished1239ABC2018-07-02156/12/2018 12:2924
3Finished1236ABC2018-07-02126/12/2018 12:2924
4Finished1237ABC2018-07-02136/17/2018 12:2925
5Finished1238ABC2018-07-02146/17/2018 12:2925
6Finished1239ABC2018-07-02156/17/2018 12:2925
7Finished1240ABC2018-07-02226/17/2018 12:2925


246/17/2018 12:2925





MaterialMaterial TypeOrder NumberSupplierDelivery DateDelivery Quantitysnapshot DateCurrent WeekOrder previous WeekSnapshot DatePrevious Week NoPrivious Week Delivery DatePrevious Week QuantityRemarks
4Finished1237ABC7/12/2018136/17/2018 12:292512376/12/2018 12:29242018-07-0210 
4Finished1237ABC7/12/2018136/17/2018 12:292512376/6/2018 12:29232018-07-0213 
 1Finished 1234ABC 2018-07-02 156/6/2018 12:292312346/6/2018 12:29232018-07-0215Deleted from Current Week
8Finished1241ABC2018-07-02246/17/2018 12:2925     New Order

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]


Multi-Row Formula Tool.JPG


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 number of rows before/after the current one! 


Hope that helps.







Attaching a sample workflow for your case. Hope this helps. 



Thank you so much for the solution.. :)

Thank You so much this really helped me to ease the work 5 Stars for you :)


@Shank, would like to know the solution which helped you really:)

Actually, You'r logic perfectly worked for me...

Hi @NicoleJohnson


Thanks for the detail explanation and more insight on the multi-row tool.


I learned the multi-row tool usage now. thanks once again for such a wonderful explanation.