Discovering largest change in performance in a group

#1

Hello,

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,

Kevin

#2

Hi Kevin,

Indeed Period Over Period is the right feature to use in this case. You can use Time Dimension on your date column to make the POP calculation seamless. For filtering, as you said, you can add category, branch, branch columns in the slicers so no aggregate is required.

#3

Thanks for confirming. I will play around with PoP a bit more. I was hoping to have a 3 month average and 12 month average. It just gets a little tedious (and guessing may impact performance?) adding 12 time periods to get the average. Is this something that may be added in future versions of Dundas? Maybe something where you select the # of periods to add in sequence? And possibly a formula to apply automatically like avg/sum to that group of periods since I don’t care about specific periods, just the average.

1 Like