I’m looking at options for discovering up/down trends within a group for various sliceable fields. For example which customers have increased or decreased their spending the most recently (possibly sliced by category etc.). My first thought was to get the previous full month of sales per customer and compare to monthly average for the 3 (or 6/12 etc.) months prior to previous month. Then have a table listing the top 20 customers with largest change, sorted by change %, either for top/bottom.
I know how to do this in PostgreSQL with window functions to get moving averages etc., but trying to figure out if there is a way to accomplish this in Dundas with the raw sales data (invoice line level with customer, sale date/time, sale amount, other ) grouped by a field like customer.
I played around with Period over Period to get previous month, month-2, month-3, month-4, then added a formula to average months 2/3/4 and compare to ‘previous month’. While this works, I feel like there might be a more elegant solution I’m missing? So the end result would be:
Customer | prev month | avg 3 months prior | diff | diff %
Bob | $10 | $7 | $3 | 43%
Joe | $10 | $12 | $2 | -17%
… and so on
Furthermore, it will all be sliceable by various fields like category/branch/brand, which is why I’m not pre-aggregating monthly data just for customers.
Thanks for any input,