Cleaning Data Automatically

To create forecasts for the future you need data from the past, which usually means the sales history of a product from a certain time period. This data is gathered from SAP ERP or APO DP via CPI-DS or by uploading of CSV files using the Data Integration Jobs app. When the data is available, it can be checked for missing values, outliers, and promotion-related sales lifts, and consolidated with the help of the pre-processing algorithms selected for the forecast model you’re using.

There are three main methods to cleanse your data:

• Substitute Missing Values
• Automatic Outlier Correction
• Outlier Correction
• Promotion Sales Lift Elimination

An "outlier" is a value in the historical data that lies outside the expected range of values. There can be a variety of reasons for this deviation, for example a data entry error or a one-time event that affected the sales results.

There are two models in SAP Integrated Business Planning (SAP IBP) that help to detect outliers in your historical data:

1. Interquartile Range Test: The system checks whether the time series values are within the interquartile range, which is the difference between the third quartile and the first quartile of the data. The values that are not within this range are identified as outliers.

   Given a series of numeric data, the inter-quartile range (IQR) is the difference between the third quartile (Q3) and the first quartile (Q1) of the data: IQR = Q3 – Q1’.

   Q1 is equal to the 25th percentile and Q3 is equal to the 75th percentile. The p-th percentile of a numeric vector is a number, which is greater than or equal to p% of all the values of this numeric vector.

   IQR Test is a method to test the outliers of a series of numeric data. The algorithm performs the following tasks:

   • Calculates Q1, Q3, and IQR
   • Sets the upper and lower bounds as follows:
     • Upper-bound = Q3 + 1.5 × IQR
     • Lower-bound = Q1 – 1.5 × IQR
   • Tests each value in a numeric vector to determine if it is in the range. The value outside the range is marked as an outlier, meaning it does not pass the IQR test
2. Variance Test: The system checks whether the time series values deviate from the mean by more than the standard deviation multiplied by a constant. The values showing larger deviations are identified as outliers.

   Variance Test is a method to identify the outliers of n number of numeric data {xi} where 0 < i < n+1, using the mean {μ} and the standard deviation of {σ} of n number of numeric data {xi}.

   Following steps are performed for the Variance Test:

   • Calculate the mean (μ) and the standard deviation (σ):
   • Set the upper and lower bounds as follows:
     • Upper-bound = µ + multiplier * ϭ
     • Lower-bound = µ – multiplier * ϭ
     Where the multiplier is a coefficient provided by the user to test whether all the values of a numeric vector are in the range. If a value is outside the range, it means it does not pass the Variance Test. The value is marked as an outlier

If data values are out of bounds, the system corrects the values. The following figure shows the upper-bound and the lower-bound.

In addition to the classic Outlier Correction method, SAP IBP, advanced demand offers an algorithm for automatic outlier correction. It detects and adjusts abnormal spikes or dips in historical demand, without the necessity to provide detailed detection parameters. The system leverages the power of machine learning to automatically detect and correct outliers in time series data before forecast calculations are run.

Remark: When it comes to seasonal products, automatic outlier correction might by difficult and needs to be carefully validated as the values might get falsified as the season could get detected as outlier.

• Full Horizon: Substitution will take place for historic and future data contained in the specified key figure
• History Only: Substitution will take place for historic data contained in the specified key figure
• Future Only: Substitution will take place for future data contained in the specified key figure

You can add the Substitute Missing Values algorithm more than once to your model. For example, you may want the system to substitute missing sales history values from the past with the mean of the existing values, and missing price values from the past and the future with the median of the existing values.

• Correction with Mean Excluding Outliers

• Correction with Tolerance Excluding Outliers

• Correction with Median Excluding Outliers

• Correction with Mean

• Correction with Tolerance

• Correction with Median

Promotion Sales Lift Elimination is an automatic correction of positive outliers (sales lifts), which are associated with promotions. The algorithm combines the outlier detection logic used by the outlier correction algorithm and additional input key figure for sales lift. The algorithm searches for correlations between the two types of input, to identify the periods when outliers were caused by promotions. These identified values are eliminated from the sales history.

Despite the availability of automatic sales lift elimination, it is recommended to analyze the business context and availability of information. Very often outliers from a statistical point of view are not outliers from a business point of view. If they can be explained by additional variables (e.g. promotions) then it is best to not remove them, but keep them in the sales history and provide the forecasting algorithm the context via external variables. Correcting these values could potentially even reduce the forecast accuracy.