This example describes how you can use the PREV function to analyze data that is available in a window in rows before the current one.

Functions:

The following dataset contains orders for multiple customers over a period of a few days, listed in no particular order. You want to assess how order size has changed for each customer over time and to provide offers to your customers based on changes in order volume.

Source:

DateCustIdOrderIdOrderValue
1/4/16C001Ord002500
1/11/16C003Ord005200
1/20/16C002Ord007300
1/21/16C003Ord008400
1/4/16C001Ord001100
1/7/16C002Ord003600
1/8/16C003Ord004700
1/21/16C002Ord009200
1/15/16C001Ord006900

Transformation:

When the data is loaded into the Transformer page, you can use the PREV function to gather the order values for the previous two orders into a new column. The trick is to order the window transform by the date and group it by customer:

You should now have the following columns in your dataset: DateCustIdOrderIdOrderValueOrderValue_1OrderValue_2.

The two new columns represent the previous order and the order before that, respectively. Now, each row contains the current order (OrderValue) as well as the previous orders. Now, you want to take the following customer actions:

To address the first one, you might add the following, which uses the IF function to test the value of the current order compared to the previous ones: 

You can now see which customers are due a rebate. Now, edit the above and replace it with the following, which addresses the second condition. If neither condition is valid, then the result is send holiday card.

Results:

After you delete the OrderValue_1 and OrderValue_2 columns, your dataset should look like the following. Since the transformations with PREV functions grouped by CustId, the order of records has changed.

DateCustIdOrderIdOrderValueCustomerAction
1/4/16C001Ord001100send rebate
1/7/16C001Ord002500send rebate
1/15/16C001Ord006900send rebate
1/8/16C003Ord004700send rebate
1/11/16C003Ord005200send rebate
1/21/16C003Ord008400send coupon
1/7/16C002Ord003600send rebate
1/20/16C002Ord007300send rebate
1/21/16C002Ord009200send coupon