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
by Charley Kyd
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
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.
The first month to be displayed in the chart.
The beginning month chosen for this data series.
The product to be displayed, as text.
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,
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
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
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.