Display graph without weekends

[Petar]

Hi,

I have a problem with bar charts. I have values only on working days and wish to plot only that data (without weekends).
Hereafter is a sample of my code:

Code: Select all

With AxTChart
	.AddSeries(TeeChart.ESeriesClass.scBar)
	.Series(IntSerie).asBar.BarStyle = TeeChart.EBarStyle.bsRectGradient
	.Series(IntSerie).XValues.DateTime = TRUE
	.Series(IntSerie).asBar.BarWidthPercent = 100

End With

For i = 0 To oData.Count - 1
       eElement = oData.Item(i)
	AxTChart.Series(IntSerie).AddXY(DateValue(eElement.Date).ToOADate(), eElement.Value, "", AxTChart.Series(IntSerie).Color)
Next i

If I 'm using above code I will have Friday Bar larger 3 times the others bars, because it includes Saturday and Sunday, even though they are null values.

If I force Sunday and Saturday values to be 0 or Nothing , Friday Bar will be OK, but there will be a gap between Friday and Monday.

I was inspired by a solution in the forum to change Series(IntSerie).XValues.DateTime to FAlse and add points AS 1,2,3 ....
with dates as corresponding lables, but this will cause problems in my tool to detect missing data and compare 2 separate graphs.
It's critical for me to have XValues.DateTime = True.

Do you have any solution?

Thank you

[Petar]

Examples

[Sandra]

Hello User1,

You can do something as next code for get a chart without weekends:

Code: Select all

    Private NotDateTime As Boolean
    Private value As Integer
    Private tmp As Integer

Private Sub Check1_Click()
If Check1.value = 1 Then
    AddAsNoWeekend
  Else
    AddAsDatetime
  End If
End Sub

Private Sub Form_Load()
      TChart1.Aspect.View3D = False
      AddAsDatetime
    End Sub
Private Sub AddAsDatetime()
Dim tmpopen, tmp As Integer
  ' This option simulates a sequential datetime Axis
  ' Candle Series
  NotDateTime = False
  TChart1.RemoveAllSeries
  TChart1.AddSeries scBar
  TChart1.Axis.Bottom.Labels.Angle = 90
    With TChart1
            .Series(0).asBar.BarStyle = TeeChart.EBarStyle.bsRectGradient
            .Series(0).XValues.DateTime = True
            .Series(0).asBar.BarWidthPercent = 50
            .Series(0).Marks.Visible = False
    End With

  With TChart1.Series(0)
    .Clear
    tmpopen = 1000 + Rnd(100)
    For t = 0 To 14
      tmp = Int(100 * Rnd - 50)
      .AddXY Format(Now - 15, "000000") + t, tmpopen, "", vbRed
      
      tmpopen = tmpopen + tmp
    Next t
  End With
End Sub

Private Sub AddAsNoWeekend()
  'Create an array of example dates
Dim myDates(14) As Variant
Dim StartDate As Date
  ' If this were to have a database datasource it would be simpler
  ' as you could use Series(xx).LabelsSource
  ' If using ADO Recordset the following principle would apply, replacing the
  ' Array with a step-through the Recordset
  NotDateTime = True
  StartDate = Now - 15
  For i = 0 To 13
    While (Weekday(StartDate) = 1) Or (Weekday(StartDate) = 7)
      StartDate = StartDate + 1
    Wend
    myDates(i) = Format(StartDate, "d-mmm-yyyy")
    StartDate = StartDate + 1
  Next i
  TChart1.RemoveAllSeries
  TChart1.AddSeries scBar
  
    With TChart1
            .Series(0).asBar.BarStyle = TeeChart.EBarStyle.bsRectGradient
            .Series(0).XValues.DateTime = True
            .Series(0).asBar.BarWidthPercent = 50
             .Series(0).Marks.Visible = False
        End With
  With TChart1.Series(0)
    .XValues.DateTime = False
    .Clear
    tmpopen = 1000 + Rnd(100)
    For t = 0 To 8
      tmp = Int(100 * Rnd - 50)
      .AddXY 1 + t, tmpopen, "", vbRed
      .PointLabel(t) = myDates(t)
      tmpopen = tmpopen + tmp
    Next t
  End With
End Sub

Could you tell us if previous code works as you want?

Thanks,

[Petar]

Hi Sandra,

as I mentioned in my 1st post:

"...I was inspired by a solution in the forum to change Series(IntSerie).XValues.DateTime to FAlse and add points AS 1,2,3 ....
with dates as corresponding labels, but this will cause problems in my tool.."


and so I cannot use your solution:

Code: Select all

      .XValues.DateTime = False
      .AddXY 1 + t, tmpopen, "", vbRed
      .PointLabel(t) = myDates(t)

we.png (32.88 KiB) Viewed 11782 times

I'll explain you why:
1) Example: Chart historic daily values of traded Volume from 01/01/09 to 31/12/09 . If I have missing daily data for a period of 1 month (ex Apr09) and I plot data with your solution, i will not detect easily the missing data because 31/03/09 and 01/05/09 will be next each other and there will be no gap between them. So everything will appear to be correct.

2) It will still be difficult to compute spread between 2 graphs plotted with your code if 1st graph have daily values and 2nd only (Monday and Friday values)

That's why I'm interested to have .XValues.DateTime = True , and I'm looking for an option to not show Weekends on DateTime type axis.

Thanks,
User1

[Sandra]

Hello User1,

The solution above allows change format of DateTime for example dd/mm/yyyy as do in line below:

Code: Select all

myDates(i) = Format(StartDate, "dd/mm/yyyy")

Moreover, if you want a month without dates you can add nulls in its points. For example if you want April without dates, you can add nulls the points correspond April.

Thanks,

[Petar]

Hi,
It's not working for me:

results.png (26.52 KiB) Viewed 11751 times

values.png (17.43 KiB) Viewed 11743 times

I cannot understand what I'm missing. I add Null values on weekends,
but I still see week-end dates and gap between friday and monday. Hereafter a sample code:

Code: Select all

With AxTChart
	.AddSeries(TeeChart.ESeriesClass.scBar)
	.Series(0).asBar.Gradient.Visible = True
	.Series(0).asBar.BarStyle = TeeChart.EBarStyle.bsRectGradient
	.Series(0).asBar.BarWidthPercent = 60
	.Series(0).Marks.Visible = False
	.Series(0).XValues.DateTime = True
end with

AxTChart.Series(0).AddXY(Math.Round(DateValue("26/11/10").ToOADate(), 0), 120, "", AxTChart.Series(0).Color)
AxTChart.Series(0).AddNullXY(Math.Round(DateValue("27/11/10").ToOADate(), 0), 0, "")
AxTChart.Series(0).AddNullXY(Math.Round(DateValue("28/11/10").ToOADate(), 0), 0, "")
AxTChart.Series(0).AddXY(Math.Round(DateValue("29/11/10").ToOADate(), 0), 150, "", AxTChart.Series(0).Color)

Any idea? Can you give me an example?

Thanks

[Yeray]

Hi User1,

Adding values with AddXY method gives you the opportunity to control where exactly in the axis will be drawn each bar. And I think that's what you need.
If you set the bottom axis to be DateTime, you still have to control the separation between one bar and the next one as DateTimes are in fact doubles. It's the expected behaviour to see more separation between two bars with a weekend increment in XValue than between two consecutive days bars.

Adding the date as XValue (or adding Null values), doesn't allow you to control this separation. That's why I recommend you to use a variable to control the XValue and increment its value in one unit from Friday to Monday, the same as from Monday to Tuesday.

And when you want to add a gap between bars (for example to showing that there is no data in a month) you simply have to increment the XValue in more units.

Code: Select all

Private Sub Form_Load()
  TeeCommander1.ChartLink = TChart1.ChartLink
  TeeCommander2.ChartLink = TChart2.ChartLink
  
  TChart1.Aspect.View3D = False
  TChart1.Legend.Visible = False
  
  TChart2.Aspect.View3D = False
  TChart2.Legend.Visible = False
  
  TChart1.AddSeries scBar
  TChart2.AddSeries scBar
  
  Dim XValue, YValue As Double
  Dim i As Integer
  Dim myDate As Date
  
  XValue = 0
  
  myDate = DateValue("01/01/2010")
  For i = 0 To 89
    If Weekday(myDate) <> vbSaturday And Weekday(myDate) <> vbSunday Then
      YValue = Rnd * 100
      If Month(myDate) <> 2 Then
        TChart1.Series(0).AddXY XValue, YValue, Str$(myDate), clTeeColor
      End If
      
      TChart2.Series(0).AddXY XValue, YValue, Str$(myDate), clTeeColor
      XValue = XValue + 1
    End If
    myDate = myDate + 1
  Next i
  
  TChart1.Environment.InternalRepaint
  TChart2.Environment.InternalRepaint
  
  TChart1.Series(0).asBar.BarWidth = TChart2.Series(0).asBar.BarWidth
End Sub

Bars.png (84.81 KiB) Viewed 11733 times

So adding the following to the code above doesn't affect the result:

Code: Select all

  TChart1.Series(0).XValues.DateTime = True
  TChart2.Series(0).XValues.DateTime = True

If you need a more precise example, please try to arrange a simple example project we can run as-is to reproduce the behaviour here, showing us something similar to the two charts you are trying to obtain and the tool you are using.

[Petar]

Thank you,

however it's still not what we were looking for. I think that new version of TeeChart has a new display tool : Vertical Break Lines and you can specify the number of points you want to hide after specific point (in this case Friday).
But I think for the moment we will change our tool and control the X axis AddPointXY ( with 0,1,2....) as you bough suggested.

Thanks

User1