Creating leading and lagged variables for retail data

[dgogue]

Hi everyone, 

Brand new to Alteryx here (first post!) and have a question about creating leading/lagged variables from retail data.  I am trying to measure how the velocity (sales rate) of an item in Month X impacts the Distribution in Month X+1 (the following month).  A simple example of the made up data is below:

Month  |  Sales Rate | Distribution

A  | 5000 | 50

B | 10000 | 80

C |  7500 | 40

The resulting solution I am looking for would create something like the below:
Month  |  Sales Rate | Distribution | Sales Rate Previous Month

C | 7500 | 40 | 10000

I searched leading and lagged variables and the post i found was less than helpful. I hope this query makes sense. any help welcome!

[AkimasaKajitani · Accepted solution]

Hi @dgogue 

In this case, you can use Multi-Row Formula tool.

This tool can take the previous or next row's value.

Result : 

[john_watkins · Accepted solution]

@AkimasaKajitani  has the correct answer when trying to perform those actions inside of the Alteryx components/tools.   Keep in mind that you can also run your lag/lead, or any other analytic function directly if using any of the in-db tools.  You simply create that statement as a formula just as you would adding it to your select clause in SQL.   Multi-row works great when you have a reasonable data set, but if you are not sure about variability in rows sometimes there are better dynamic ways to work it.  Remember to sort your data how you need it before running the multi-row tool!

[atcodedog05 · Accepted solution]

Hi @dgogue 

Adding to @AkimasaKajitani reposnse in a similar way you can get next month's sales rate by referencing the next row ([row+1]). And you can set the non existant value as either null or 0 (Previous month of first month or next month of last month) refer to the highlighted.

@john_watkins also adds a great point that make sure the data is sorted.

Workflow:

Here is an interactive lesson on multi-row formula tool.

https://community.alteryx.com/t5/Interactive-Lessons/Multi-Row-Formula/ta-p/82872

Hope this helps : )