Alteryx Designer Desktop Discussions

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

Efficient method to summarize sales by various trailing periods

mattkimball
5 - Atom

Is there an efficient way to summarize data by different trailing time periods? My ideal output is each row being a unique customer and each column being a time period with a sum or a flag. Like this: 

 

Customer | Sales -11 Months | Sales -10 Months | Sales -9 Months |...n | Sales current month |

 

Currently I'm doing this by creating flags on a calendar table via formula tools, joining that to transaction data, then creating a filter for each period/flag (e.g., Sales -5 Months = 1), summarizing each one, then multiple joining all the summarize tools together. This seems inefficient and I'm thinking you might have a better way. Especially since I want to add several more variables in the same each-month structure (i.e., order count, SKU count, etc.). My current approach would end up with 12 filter/summarize tools per variable. 

 

This might be shooting for the stars: would there further be a way to plug in a few periods and a table similar to above is created. An example might be for sales Prior 3 months, Prior 6 months, prior 9 months, etc. 

 

Many thanks in advance. These discussion boards have been super helpful to me.

2 REPLIES 2
binuacs
21 - Polaris

@mattkimball can you provide some sample data and expected result for better understanding?

cpet13
11 - Bolide

@mattkimball this sounds like something an iterative or a batch macro might be able to solve. In essence, you could have a number of variables (5 months, 6 months, 7 months, etc.) and the workflow runs for each variable/timeframe. You can learn about macros here, and there are also some great interactive lessons on macros. Macros (alteryx.com)
Interactive Lessons - Alteryx Community

Labels
Top Solution Authors