Designing Moving Averages for Your Crystal Xcelsius dashboards

By Loren Abdulezer, CEO, Evolving Technologies Corporation
a Crystal Xcelsius Consulting Partner
www.XcelsiusBestPractices.com
718-875-1718

Abstract

A dashboard represents the intersection of visualization, interactivity, and analytics; hence, the idea of visual analysis. An important aspect in time series data is to show information and compute moving averages for such data. While this capability exists within Excel, its interactive capability is not easily harnessed in spreadsheets. This article explains how to prepare and represent interactive moving averages in your Crystal Xcelsius dashboards.

It's Interactive!
Figure 1: Moving Average Dashboard.

Overview

A key objective of a dashboard is to create an interactive environment that presents your business information in a logical and visually appealing fashion. Discerning actionable conclusions from the data can be a challenging task, especially if it's noisy or highly seasonal. If you are monitoring online sales for one of your company's products, you may see a dip in sales levels during weekends. Despite the dips and statistical fluctuations in sales levels, there may be a consistent trend. How easy is it to spot that trend? If you can "smooth out" that data, the trend may be evident and simple to spot; and that's specifically what a moving average accomplishes.

A moving average works by "smearing out" the data over some specified period of time. Exactly how much smearing or smoothing should you apply? That's a good question, and one that dashboards are ideally suited to assist you in answering.

Figure 1 features a dashboard with a slider that is set to interactively adjust the moving average period. You can also move through a timeline by clicking on the Interactive Calendar component.

We'll be modifying this dashboard throughout the article. Feel free to follow along by downloading the source files here.

Calculating Moving Averages inside a Spreadsheet
Imagine having data in a timeline as follows:

Date	Units Sold
8/1/2005	4290
8/2/2005	4384
8/3/2005	4466
8/4/2005	4348

A three day moving average for 8/1/2005 could be defined as:

(4290+4384+4466)/3

The three day moving average for 8/2/2005 could be set to:

(4384+4466+4348)/3

Figure 2 shows this done in a spreadsheet.

The two yellow highlighted cells are values that are adjusted by the Crystal Xcelsius dashboard. Cell G1 adjusts the moving average period, while cell A8 is the starting date that appears in the line chart of Figure 1, and is adjusted by clicking on dates inside the Interactive Calendar component.

Figure 2: Setting up a range of Moving Average values for Crystal Xcelsius.

The data that is used in column B is retrieved from source data using a VLOOKUP formula. The formula in cell B8 is:

=VLOOKUP($A8,Sheet1!A8:B$420,2)

It is looking up the data for a specific date. You can adjust this formula to point it to your data and replicate the formula to the cells immediately below it.

Column D computes the moving average using the formulas displayed above. These values are then plotted along with the un-averaged values using a Crystal Xcelsius line chart.

On the dashboard side, the moving average can be adjusted by a Slider component (see Figure 3). This enables us to dynamically increase or decrease the date range of our model, thereby altering the moving average for our units sold.

Figure 3: Link the Slider component value to cell G2 of Sheet 2.

Closing Thoughts

Moving averages and smoothing techniques can be easily varied. For instance in this article, the averages are looking ahead of a specific date. In other words, it is anticipatory. What's to stop you from creating a moving average dashboard that's retrospective in nature (that is, looking back upon the previous days)? Or even a combination of both?

There are also techniques for compensating for missing data in the timeline, to avoid needless skewing of moving averages.

Whatever the technique, you now have the basic framework for handling moving averages in your Crystal Xcelsius dashboards.

Loren Abdulezer is the CEO of Evolving Technologies Corporation (ETC), and author of the best-selling Excel Best Practices for Business and the recently published Escape from Excel Hell.

ETC, an Xcelsius Consulting Partner, is a technology consulting firm based in New York City. More information about Xcelsius can be found on Loren's web site: XcelsiusBestPractices.com. He can be reached at la@evolvingtech.com.

The files referenced in this article can be found in the Article Reprints section of: http://www.xcelsiusbestpractices.com.

©2006 Evolving Technologies Corporation - all rights reserved.