How to: Show limits and a trend in a chart

Draw a tolerance band and a minimum in a chart, show several measuring points as one series each, count records per month as columns, and read the slope and the date when a limit will be reached.

Requires WKL Forms Object Library 1.60 or later.

• AddReference FromValue, [ToValue], [Caption], [Kind] adds a limit line (one value) or a band (two values) in the color of a status kind (wskOk, wskWarn, wskBad, wskInfo). The value axis always includes every limit and band. ClearReferences removes them all.
• Wide or long data: YFields = "Wall;Nominal" creates one series per field; YFields = "Wall" with SeriesField = "Point" creates one series per value of Point.
• Aggregation: Aggregate (wcaSum, wcaAverage, wcaCount, wcaMin, wcaMax) with Period (wcyDay … wcyYear) groups a date axis. wcaCount needs no Y field.
• Trend: with ShowTrend = True, each series gets a dashed least-squares line; TrendSlope(i) returns the change per X unit (per day on a date axis), and TrendReaches(i, Value) returns the date when the line reaches Value, or Null.

All three charts are set up in Form_Load of a form with the WKL Chart controls chtWall, chtAll, and chtMonth; the tables are the ones from the tutorial (Show a measurement series with its limit and trend) plus a query qryReadingPoint (SELECT tblReading.ReadDate, tblReading.Wall, tblPoint.Name AS Point FROM tblReading INNER JOIN tblPoint ON tblReading.PointID = tblPoint.ID).

Option Compare Database
Option Explicit
 
Private Sub Form_Load()
    On Error GoTo ErrorHandler
 
    ' One point with its tolerance band and minimum.
    With Me!chtWall.Object
        .RecordSource = "SELECT ReadDate, Wall FROM tblReading WHERE PointID = 1"
        .XField = "ReadDate"
        .YFields = "Wall"
        .ShowTrend = True
        .ClearReferences
        .AddReference 9, 10, "Toleranz", wskOk
        .AddReference 8.5, , "Minimum", wskBad
    End With
 
    ' All points, one series each, from long data.
    With Me!chtAll.Object
        .RecordSource = "qryReadingPoint"
        .XField = "ReadDate"
        .YFields = "Wall"
        .SeriesField = "Point"
    End With
 
    ' Measurements per year as columns.
    With Me!chtMonth.Object
        .RecordSource = "tblReading"
        .XField = "ReadDate"
        .ChartType = wctColumn
        .Aggregate = wcaCount
        .Period = wcyYear
    End With
 
FinalHandler:
    Exit Sub
 
ErrorHandler:
    MsgBox Err.Description, vbExclamation
    Resume FinalHandler
End Sub
 
Private Sub chtWall_DataLoaded(ByVal Points As Long)
    On Error GoTo ErrorHandler
 
    ' Slope per day, shown per year; the date the minimum is reached, or Null.
    Debug.Print "Per year: "; Format(Me!chtWall.Object.TrendSlope(0) * 365.25, "0.00"); " mm"
    Debug.Print "Minimum on: "; Nz(Me!chtWall.Object.TrendReaches(0, 8.5), "never")
 
FinalHandler:
    Exit Sub
 
ErrorHandler:
    Debug.Print Err.Description
    Resume FinalHandler
End Sub

• Setting the properties one at a time makes the chart read the data again after each change; a DataLoaded with a LastError along the way is normal while the settings are incomplete. Setting them in Design view (or in the designer) avoids this.
• A row without a readable X value isn't drawn; RowsWithoutX counts it.
• A missing Y value breaks the line (Missing = wcvGap); wcvConnect skips the point, and wcvZero treats it as 0. A period without any value is a gap, not 0.
• More than 2,000 points on a line are downsampled for display, and the chart says so; the trend and the axis still use every value.
• Clicking a legend entry hides or shows its series (SeriesVisible); clicking a point raises PointClick(Series, X, Y).

• WklChart overview: the full contract.
• Show a measurement series with its limit and trend