Right now, the power to solve new global challenges across industries, is at your fingertips, no matter where you're working from. Create a new topic or reply to an existing thread to share your perspective.
We are currently experiencing an issue with Email verification at this time and working towards a solution. Should you encounter this issue, please click on the "Send Verification Button" a second time and the request should go through. If the issue still persists for you, please email support@alteryx.com for assistance.

Industry Discussions

A dedicated space to share resources, connect with like-minded data workers, and discuss industry specific analytic challenges + solutions.
SOLVED

Creating leading and lagged variables for retail data

dgogue
7 - Meteor

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!

 

 

3 REPLIES 3
AkimasaKajitani
15 - Aurora

Hi @dgogue 

 

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

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

 

AkimasaKajitani_0-1628171372025.png

 

Result : 

AkimasaKajitani_1-1628171430803.png

 

 

john_watkins
10 - Fireball

@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
20 - Arcturus

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:

atcodedog05_0-1628171887851.png

 

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 : )