Home > OLAP and Excel > 

An Excel Tutorial:

Set Up Common-Age Charts
With Excel and OLAP Data

Common-age charts track the early sales performance of new
products. An Excel-friendly OLAP database can provide the data
easily.

In Use Common-Age Charts To Compare New Products I showed how an Excel chart can display sales in the months after each product was introduced. This article describes a more general approach, using formulas that return data from an Excel-friendly OLAP database.

With this approach, the formulas tend to be more powerful, more flexible, easier to create, and less prone to error.

To illustrate,  the following figure includes a chart showing four data series. Each series begins on a different calendar month, specified in the range A9:A12. This month might specify when the product was introduced, when a new advertising program began, when a new sales manager was hired for each product shown, and so on.

The figure doesn't show the data for columns 6 through 12, ending in column Q. Otherwise, the figure displays the entire worksheet.

As always, we used PowerOLAP here. But other Excel-friendly OLAPs have similar capabilities.

Here is a brief explanation for the key cells in this display:

I used the Create Names dialog to assign the labels in cells A1:A6 to the adjacent cells in column B.

Cells B1 and B2 contain the labels shown in the figure. They specify the OLAP database file and the cube within that database.

B3:   =OLAPMember(Database, "SalesView",2)
B4:   =OLAPMember(Database, "SalesMeasure",2)
B5:   =OLAPMember(Database, "Unit", 1)

These PowerOLAP functions return the member name for each dimension specified. To choose another member name, double-click on one of these cells and choose a member from the dialog that PowerOLAP displays.

B6:   3/1/2000
The first month to be displayed in the chart.

A9:   3/1/2000
The beginning month chosen for this data series.

B9:   1200-5
The product to be displayed, as text.

C9:   =ROUND(($A9-StartMo)/31,0)
The number of months between the beginning month for this row of data and the first month to be displayed in the chart.

D9:   =C9&", "&TEXT($A9,"m/yy")
The text to be displayed in the chart's legend: The product, a comma, and the product's beginning month and year.

E9:   =OLAPReadWrite(Database, Cube, SalesView, SalesMeasure,
           Unit, CurMo, $C9)
A PowerOLAP function returning the sales value as defined by the range names and cell references shown.

To complete the dataset, enter the data shown in cells A10:B12. Copy cell E9 to the range E9:Q9. Then copy the range C9:Q9 to the range C10:12.

To create the chart, temporarily erase the label from cell D8. Select the range D8:Q12. Launch the Chart Wizard by clicking on its icon in your formula bar or by choosing Insert Chart. Then complete your chart as desired.

If you want to use colors other than those in Excel's standard setup, refer to the ExcelUser article, Display Any Colors in Excel.

With this report set up as shown in the figure, you can view it other perspectives merely by changing the values in cells B3 through B6 in the figure. For example, to print a similar chart for the following month, you would double-click on cell B6, choose the month you want, then recalculate and print.