Is there a way to remove outliers from data in gridview?

I've created a calculated column which averages unit sales from LY, the last 13 periods (months) and YTD sales (weighted by how far into the year we are) to predict item sales for the year. I use this value to suggest ordering quantities to keep a few month supply in stock. I run into a problem here and there when I have an oddball order for a large qty which throws these values off and suggests ordering items I won't necessarily move for a while. I don't know if gridview has any built it functions for removing these outliers from the equation or if there is a more round about way of accomplishing that (without having to export to excel). Here is my existing equation, I filter it for values >= 1

(
{Units Sold LY}
+
(({Units Sold YTD} + {Qty on SO})*(today()-beginyear())/365
+
({Units Sold Period 1}+{Units Sold Period 2} +{Units Sold Period 3} +{Units Sold Period 4} +{Units Sold Period 5} +{Units Sold Period 6} +{Units Sold Period 7} +{Units Sold Period 8} +{Units Sold Period 9} +{Units Sold Period 10} +{Units Sold Period 11} +{Units Sold Period 12}+{Units Sold Period 13}+{Qty on SO})*(12/13)
)
/
(2+(today()-beginyear())/365)

Hi Chris:

Putting aside outliers for the moment, I think the formula as displayed needs to be revisited. That formula includes some double-counting because the 13 Period figures are part of the Last Year (LY) figures and the YTD figures.

I used Excel to test the formula. I assumed a product that consistently sold 20 units per month for 24 months and created 24 monthly columns. Using the formula I consistently ended up with a result over 700. Since the product only sold 240 per year, that result isn't helpful.

It's possible that I missed a bracket or an operator, but wouldn't it be simpler to just use the 13 periods (which are the current period and the previous 12)?

Hello Bruce,

you must have had some parenthesis off, I created a spreadsheet to use as an example of the formula to show that it works-

example

My sales aren't consistent on a month to month basis, so I am using three different indicators to project sales (LY, YTD & last 13 periods) and averaging them to get a better indicator than just one dataset alone.

Chris:

Your results look better than mine. Your projected sales for the year = 240 which makes sense.

But what if you are at the end of the first month of the year? YTD equals 20 (change cell E3 to 20) and the new projected sales = 170. Based on the 13 months of history, you would expect it to remain at 240

Your idea (3 different indicators) is good. But they aren't that different. For instance, in month #1 of the year, you only have one month of YTD and 12 months of LY. Those 13 months are identical to the 13 Period figures (assuming year-end and period-end figures are run on time). In month #12 of the year, you have the 13 Periods plus 11 months of LY.

The Excel sample sheet i provided only adapts correctly is you change the values in the Qty column. A change in the E3 column would also have to be reflected in the E4 column. When your sales are more sporadic, the three data sets result in more diverse figures between the them (LY, last 13 periods * 12/13, & projected sales based on ytd * 365/#day of the year ). I used these three sets of data because they are what's available in Gridview, if I could use sales from further back I would have worked them into the equation as well. All of this is besides the point though, the main question is whether or not gridview has a statistical function to rule out unusually high figures in the datasets to further increase the accuracy of projected sales. If not in gridview, is projected sales something that could be incorporated into sales analysis or inventory control as a report? My formula is just what I cobbled together myself with what was available to me, I'm sure there are better algorithms out there that could be incorporated into the adagio software.

Hello Chris,

How do you define "outlier"? I can think of several definitions off the top of my head:

• Unusual invoice
• Unusual month
• Unusual year

It depends a bit on the granularity of the data you are inspecting.

My approach would be to create an View against the SalesAnalysis transaction data that summarizes the sales of each item by day/month/quarter or year. The historical numbers do not change (we hope), so this View only has to be generated once per month. Use a Summary Filter to get one row per item/period. It is easy to calculate the Average for each period. Then, export this View to Excel and use Excel to calculate the median or standard deviation or whatever statistic you are going to use to identify an "outlier".

You can then use this Workbook as a View to compare against in your analysis.

Hello Andrew,

Thank you for the advise, I will try your idea and see if it helps eliminate some of the stocking suggestions for items I've only sold once all year-

by outliers I was referring to unusual invoices. I was hoping for a solution that didn't require an export to excel as it gets messy pretty fast when you're working with individual transactions on over 30K items. I was really hoping GV could do the heavy lifting for me, haha.

Hello Chris,

GridView will do a lot of lifting, but the historical data does not change so having them in an Excel table saves the time of resumming the numbers every time you need them.