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.
