Building Trend Lines in Web Intelligence
When reporting on time series data, an analyst will often use trend lines to highlight the general shape of the data and to smooth out the noise. Since trend lines come in many different varieties, an analyst will choose a specific type of trend line based on the nature and movement of the data, e.g. linear, nonlinear, seasonal, polynomial, and so on. In Part 1 of this series, we will focus on a popular method for building trend lines known as a moving average.
The moving average is a ubiquitous analysis tool used in several industries, including finance, stocks, meteorology, and industrial engineering. In a moving average, each data point is replaced with an average of the most recent n data points, where n is known as the span.
The example below shows a moving average chart published in The Wall Street Journal in April 2014. It shows the prices for iShares Nasdaq Biotech ETF over a five year period (blue line) and the corresponding moving average (red line). Notice how the moving average allows the observer to better understand the upward trajectory of the numbers while eliminating the noise introduced through day-to-day market fluctuations.
Moving averages, and trend lines in general, are easy to build using SAP Crystal Reports and Microsoft Excel, because those tools have delivered features that allow the user to add moving averages to an existing chart. Unfortunately for SAP Web Intelligence users, no such feature exists in to create a moving average. Nonetheless, this article will show how Web Intelligence report developers can still build their own robust and effective moving averages through Web Intelligence formulas.
Simple Moving Average
Consider the Web Intelligence chart below, showing average August temperatures in Phoenix, Arizona from 1948 through 2014. These data were obtained from the National Centers for Environmental Information. Download the data set here.
The chart hints at potentially rising temperatures, but the trend is mostly obscured through natural fluctuations. A moving average will help to better understand the trend of Phoenix temperatures.
A simple moving average is defined by the equation to the right, where N is the length of the time span and T is the current data point.
A Web Intelligence formula to calculate the five-year moving average (i.e. N=5) for the August temperature data is shown at right.
Web Intelligence formula:=If Not(IsNull(Previous([AUGUST AVG TEMPERATURE]; 4))) Then
(
[AUGUST AVG TEMPERATURE] +
Previous([AUGUST AVG TEMPERATURE]; 1)
+ Previous([AUGUST AVG TEMPERATURE]; 2)
+ Previous([AUGUST AVG TEMPERATURE]; 3)
+ Previous([AUGUST AVG TEMPERATURE]; 4)
) / 5
The formula can then be added to the chart, which creates a smoothed line to accompany the raw data. The moving average more clearly presents a trend of warming temperatures, indicating that August temperatures in Phoenix, AZ have risen by approximately 5 degrees over the past 66 years.
The Web Intelligence formula for simple moving averages can be simplified using the RunningSum() function, which is convenient for moving averages with very large spans.
Web Intelligence formula (simplified):=If Not(IsNull(Previous([AUGUST AVG TEMPERATURE]; 4))) Then
(
RunningSum([AUGUST AVG TEMPERATURE])
– RunningSum(Previous([AUGUST AVG TEMPERATURE]; 5))
) / 5
If desired, increase the size of N to create a smoother trend line. For example, a 10-year moving average (i.e. N = 10) will yield the result shown below.
Weighted Moving Average
A simple moving average is unweighted, meaning that all previous data points within the span are treated the same. In a weighted moving average, more recent data points are assigned larger weights and, therefore, have greater influence on the trend line. The weighted moving average is a good option for seasonal, polynomial, and other nonlinear time series.
A Web Intelligence formula to calculate the five-year weighted moving average (i.e. N=5) for the August temperature data is shown at right. Notice how the most recent data point is assigned the highest weight, 5. The previous data point is assigned a weight of 4 and the weights decrease linearly from there until the oldest data point in the span is effectively assigned no weight.
Web Intelligence formula:=If Not(IsNull(Previous([AUGUST AVG TEMPERATURE]; 4))) Then
(
5*[AUGUST AVG TEMPERATURE]
+ 4*Previous([AUGUST AVG TEMPERATURE]; 1)
+ 3*Previous([AUGUST AVG TEMPERATURE]; 2)
+ 2*Previous([AUGUST AVG TEMPERATURE]; 3)
+ Previous([AUGUST AVG TEMPERATURE]; 4))
/ ((5 * (5+1)) / 2)
Exponential Moving Average
An exponential moving average is a type of weighted moving average in which the weights decrease exponentially for older data points, rather than the simple linear decreases used in the basic weighted moving average. This makes an exponential moving average more responsive to changing trends in the data. In the equation below, N is the length of the time span and T is the current data point.
A Web Intelligence formula to calculate the five-year exponential moving average (i.e. N=5) for the August temperature data is shown at right.
Web Intelligence formula:=If [YEAR] = Min([YEAR]) In Block Then
[AUGUST AVG TEMPERATURE]
Else
(
[AUGUST AVG TEMPERATURE] * (2 / (5+1)))
+ (Previous(Self) * (1 – (2 / (5+1)))
)
Hanning Filter
The moving averages discussed previously are designed to only consider the current and previous data points. In some cases, an analyst may want to employ a centered moving average which incorporates previous, current, and future values.
A specific implementation of this technique is a 3-span centered moving average called the Hanning Filter. This is also a weighted moving average, as the current data point is assigned the highest weight and the two surrounding points are assigned equal but lesser weights.
Web Intelligence formula:=If Not(IsNull(RelativeValue([AUGUST AVG TEMPERATURE];([YEAR]); -1)))
And Not(IsNull(RelativeValue([AUGUST AVG TEMPERATURE];([YEAR]); 1))) Then
(0.25 * RelativeValue([AUGUST AVG TEMPERATURE];([YEAR]); -1))
+ (0.5 * [AUGUST AVG TEMPERATURE])
+ (0.25 * RelativeValue([AUGUST AVG TEMPERATURE];([YEAR]); 1))
Moving Median
A disadvantage of moving averages is that they tend to stray when presented with outliers and data errors. Consider the time series below of daily sales amounts for a fictitious seasonal produce company. Although the data reflects a curved trend throughout most of the chart, there is also a significant outlier on Day 41.
When a simple 5-day moving average is applied to the chart, the outlier causes the trend line to deviate significantly during the period between Day 41 and Day 45. This is because, in general, simple averages are not robust to outliers.
To resolve this problem, an analyst may use a moving median which is a good alternative to a simple moving average when outliers or data errors are present. The moving median of span 3 is defined by the equation below. Note how the moving median is centered and, therefore, includes the previous, current, and next values in the data series. As shown in the chart below, the moving median neatly ignores the outlier and stays consistent with the predominant trend.
Web Intelligence formula:=If [Day] > Min([Day]) In Block And [Day] < Max([Day]) In Block Then
(
If RelativeValue([Sales]; ([Day]); -1) Between([Sales]; RelativeValue([Sales]; ([Day]); 1))
Or RelativeValue([Sales]; ([Day]); -1) Between(RelativeValue([Sales]; ([Day]); 1); [Sales])
Then RelativeValue([Sales]; ([Day]); -1)
ElseIf [Sales] Between(RelativeValue([Sales]; ([Day]); -1); RelativeValue([Sales]; ([Day]); 1))
Or [Sales] Between(RelativeValue([Sales]; ([Day]); 1); RelativeValue([Sales]; ([Day]); -1))
Then [Sales]
Else RelativeValue([Sales]; ([Day]); 1)
)
Linear Regression
Beyond moving averages, analysts might rely on linear regression trend lines as well. For more information on this technique and how to build linear regression formulas in Web Intelligence, I recommend two articles from SAP and Alastair Gulland. In addition to those resources, there was also an informative session called Increase Your Reporting Capabilities With a Few Basic Dynamic Functions and Statistical Formulas from Jeff Young (University of Utah Health Care) at the recent 2015 ASUG SAP Analytics and BusinessObjects Conference.
Conclusion
Custom trend lines create new analytical capabilities for report developers and add real value to the Web Intelligence tool. By leveraging the techniques described in this article, report developers will be able to do more meaningful trend analysis directly within Web Intelligence without the need to export raw data and rely on secondary analysis in a separate software package.
In the second part of this series, we will discuss simple forecasting techniques for Web Intelligence reports.
