I'd like to make a "top twenty" report for the top 20 items that have seen an increase in sales (sorted by the percent of increase). I'd also like to make a bottom twenty report for the 20 items that have seen the biggest decrease in sales (sorted by the percent of decrease). So, in other words, a "winners and losers" list.
I'd also like to filter out any items that sold less than X units in the sampling period. In other words, I don't care about items that only sold one unit and now sold zero ...so it has a 100% decrease. We only want to know about good selling items that saw an increase or a decrease.
There would be a "winners and losers" list for each accounting income account.
My questions are...
1. What data would you use? For your two comparison numbers ...would you take the prior 30 days as your sample period and remove the highest 2 days (to eliminate fluke data) and average the other 28 days sales and then do the same for the same period last year? I'm thinking that removing the unusually large sales might help because we are looking for sales trends and not trying to spot anomalies (such as when one unusually big sale happened and another matching one didn't).
2. How would you go about creating this report? Could you create a custom item field that held the percent increase or decrease and then use that custom field in your report? I wouldn't want to create a separate "saved search" or separate report for every one of our 5000+ items. any other details would be helpful. Should I just wait for the new "Pivot Reports" feature in 2011.2?
We are trying to find this data so that we can expand product offerings in groups of items that are growing. We'll also be able to check our pricing and other possible problems with items that we're loosing sales on.
T