Microsoft KB Archive/289288

= INFO: Using Timescale Axes with the Office Chart Component =

Article ID: 289288

Article Last Modified on 10/23/2006

-

APPLIES TO


 * Microsoft Office Web Components
 * Microsoft Office Web Components

-



This article was previously published under Q289288



SUMMARY
The timescale axis feature of the Microsoft Office Chart component provides several benefits:
 * Data may be grouped on a time interval of your choice. You can choose to group data in day, week, month, quarter, or year intervals.
 * Timescale axes are continuous, so any missing dates in your category data are automatically filled in on the axis so that data points always appear in the correct chronological location.
 * When you use a grouping for your data, you can plot a total (such as a sum or average) for each group.

This article describes how the Office XP Chart component might automatically apply a timescale to a chart axis and discusses ways in which you can customize the timescale programmatically. A code sample is also presented to illustrate the different ways that you can use the Chart component to plot chronological data.



MORE INFORMATION
When you use dates for categories on a chart, the Chart component may automatically use a timescale for the category axis if the first, last, and middle category values are dates. When the Chart component applies a timescale axis, it also determines a grouping for your data to minimize clutter on the category axis. The Chart component considers the number of days your category set represents and uses an algorithm to determine the grouping; the Chart component may decide to use a grouping that represents a number of days, weeks, months, quarters, or years. As illustrated in the demonstration presented later in this article, you can override the default grouping by setting the GroupingUnit and GroupingUnitType properties for the axis object. Grouping information is obtained from the first (innermost) axis; therefore, the GroupingUnit and GroupingUnitType properties are ignored for all other axes.

Because time is continuous, a timescale axis plots data continuously and evenly with no gaps in time. If the Chart component automatically applies a timescale to your category axis and you want to disable the timescale functionality, you can set the GroupingType property to chAxisGroupingNone.

When your data is grouped into intervals greater than one day, the data is grouped into bins and a function is used to calculate and plot a representation of the data in each bin. The default behavior is to plot the sum of the bin values. You can set the GroupingTotalFunction property of the axis to instead plot counts, averages, minimums, and maximums for bin values.

By default, the category axis displays tick marks and labels using the same grouping that you specify for the timescale. You can modify the intervals at which tick marks and labels appear by using the TickMarkUnitType and TickLabelUnitType properties for the axis. This is useful in situations in which you want to show greater detail in the plot but also minimize tick mark and label clutter on the axis. For example, suppose that you want to show data point markers in a plot for 52 weeks using week intervals. This automatically shows 52 tick labels on the category axis. To minimize clutter for the labels on the category axis, you can change the units for the tick labels to months or quarters to display fewer labels while maintaining the correct time relationship between the plotted data and the labels. This is illustrated in the following demonstration.

Demonstration
  Paste the following code into any text editor and save it as TimeScale.htm.

 TimeScale With Missing Dates TimeScale With Year (or Week) Grouping TimeScale With Two Day Grouping  &#xa0;&#xa0; 



Dim c Set c = CSpace.Constants

Function BuildWorksheet1 'Builds a worksheet with dates in A2:A5 and numeric values representing 'sales figures in B2:B5. Dim oSheet Set oSheet = SSheet.Worksheets(1) oSheet.Cells.Clear oSheet.Range(&quot;A1:B1&quot;).Value = Array(&quot;Date&quot;, &quot;Sale&quot;) oSheet.Range(&quot;A2:B2&quot;).Value = Array(#1/1/2001#, 3000) oSheet.Range(&quot;A3:B3&quot;).Value = Array(#3/5/2001#, 1850) oSheet.Range(&quot;A4:B4&quot;).Value = Array(#3/23/2001#, 2790) oSheet.Range(&quot;A5:B5&quot;).Value = Array(#6/7/2001#, 4925) oSheet.Range(&quot;A2:A5&quot;).NumberFormat = &quot;mmm d, yyyy&quot;

End Function

Function BuildWorksheet2

'Builds a worksheet containing one year (365 days) of data in A1:B366. The first 'column represents dates and the second represents temperatures. Dim aTempBase, StartDate Dim arr(364, 1) StartDate = #1/1/2001# aTempBase = Array(25, 22, 38, 50, 60, 68, 74, 81, 62, 50, 45, 32) For d = 0 To 364 arr(d, 0) = StartDate + d       arr(d, 1) = aTempBase(Month(arr(d, 0)) - 1) + CInt(Rnd * 20) Next Dim oSheet Set oSheet = SSheet.Worksheets(1) oSheet.Cells.Clear oSheet.Range(&quot;A1:B1&quot;).Value = Array(&quot;Date&quot;, &quot;Temp&quot;) oSheet.Range(&quot;A2:B366&quot;).Value = arr oSheet.Range(&quot;A2:A366&quot;).NumberFormat = &quot;mmm dd&quot;

End Function

Function btnChart1_OnClick

BuildWorksheet1 'Bind the Chart component to the spreadsheet. Categories are in A2:A5 and 'values are in B2:B5. CSpace.Clear Set CSpace.DataSource = SSheet CSpace.SetData c.chDimCategories, c.chDataBound, &quot;A2:A5&quot; CSpace.SetData c.chDimValues, c.chDataBound, &quot;B2:B5&quot; 'Prompt to disable timescale functionality. Dim RetVal RetVal = MsgBox(&quot;Disable time scale functionality?&quot;, vbYesNo) If RetVal = vbYes Then CSpace.Charts(0).Axes(chAxisPositionCategory).GroupingType = c.chAxisGroupingNone End If End Function

Function btnChart2_OnClick BuildWorksheet2 'Bind the Chart component to the spreadsheet and plot 365 days of temperature data. 'Ccategories are in A2:A366 and values are in B2:B366. CSpace.Clear Set CSpace.DataSource = SSheet CSpace.SetData c.chDimCategories, c.chDataBound, &quot;A2:A366&quot; CSpace.SetData c.chDimValues, c.chDataBound, &quot;B2:B366&quot; 'The data is automatically grouped into months using a sum function. Change 'this to a Max function so that the maximum temperature for each month is   'plotted. CSpace.Charts(0).Axes(c.chAxisPositionCategory).GroupingTotalFunction = c.chFunctionMax 'Prompt to ask if you want to change the plot bins to weeks and use a month interval 'for the tick labels to minimize clutter on the category axis. RetVal = MsgBox(&quot;Plot data in week intervals but leave tick labels in month intervals?&quot;, vbYesNo) If RetVal = vbYes Then Dim oAxis Set oAxis = CSpace.Charts(0).Axes(c.chAxisPositionCategory) oAxis.GroupingUnit = 1 oAxis.GroupingUnitType = c.chAxisUnitWeek oAxis.TickLabelUnitType = c.chAxisUnitMonth oAxis.HasMajorGridlines = True CSpace.Charts(0).GapWidth = 300 End If End Function

Function btnChart3_OnClick

BuildWorksheet2 'Bind the chart to the spreadsheet and plot data for mid-January to mid-March. 'Categories are in A17:A70 and values are in B17:B70. CSpace.Clear Set CSpace.DataSource = SSheet CSpace.SetData c.chDimCategories, c.chDataBound, &quot;A17:A70&quot; CSpace.SetData c.chDimValues, c.chDataBound, &quot;B17:B70&quot; CSpace.Charts(0).Type = c.chChartTypeSmoothLineMarkers 'Add an additional category axis. 'The first (innermost) axis has a grouping type of days and the second (outermost) 'axis has tick lables displayed in month intervals. Dim oAxis1, oAxis2 Set oAxis1 = CSpace.Charts(0).Axes(c.chAxisPositionCategory) Set oAxis2 = CSpace.Charts(0).Axes.Add(oAxis1.Scaling) oAxis1.GroupingUnit = 2 oAxis1.GroupingUnitType = c.chAxisUnitDay oAxis1.NumberFormat = &quot;d&quot; oAxis2.NumberFormat = &quot;mmmm&quot; oAxis2.TickLabelSpacing = 1 oAxis2.TickLabelUnitType = c.chAxisUnitMonth oAxis2.TickMarkUnitType = c.chAxisUnitMonth oAxis2.HasMajorGridlines = True

End Function

Note If you are using Office 2003 you may have to change the classid accordingly.  Start Internet Explorer and browse to Timescale.htm. A Web page containing a spreadsheet and a chart is rendered. Click Timescale with Missing Dates. The sales data in cells A2:B5 in the spreadsheet is plotted in the chart. Because the data used for the category axis are dates, the Chart component automatically applies a timescale to the category axis and determines a two week grouping interval. Note that the axis shows a continuous timescale even though data for every two week interval is not represented in the spreadsheet. A message box prompts you to disable the timescale feature. Click Yes to disable the timescale (this sets GroupingType to chAxisGroupingNone) and observe the difference in the manner in which the data is plotted.</li> Click TimeScale With Year (or Week) Grouping. One year (365 days) of temperature data in cells A2:B366 of the spreadsheet is bound to the chart. The chart automatically applies a timescale and opts to group the data in month intervals. The maximum temperature value for each month is displayed because the GroupingTotalFunction property is set to chFunctionMax. A message box prompts you to plot the data in week intervals while leaving the tick labels in month intervals. Click Yes and observe the changes to the plot.</li> Click TimeScale with Two Day Grouping. The chart is bound to cells A17:B70 on the spreadsheet; A17:B70 represents daily temperature data for mid-January through mid-March. Note that two levels for the timescale axis appear: the innermost level displays in two day intervals and the outermost level displays in one month intervals.</li></ol>

<div class="references_section">