Date scaling TeeChart 5 Pro
Date scaling TeeChart 5 Pro
We have a massive database over 100,000 that hold a Date and a value. Our problem is that we have periods of missing data for example I may have a 100 values every year from 1950 to 1960 then nothing again until 1980 and then continuously since then. When the graph is displayed the is equally dividing the data points rather than scaling the graph to show the data gap. What do we need to do to properly scale the graph in accordance to the date range we have selected to display? Thanks for your help.
Hi,
are you sure you are adding your datetime XValues to the chart as values and not labels ?
are you sure you are adding your datetime XValues to the chart as values and not labels ?
Pep Jorge
http://support.steema.com
http://support.steema.com
Hi Jason,
which series type are you using ?
Using a Line series I'm able to see the gaps where there are null values using your code with a database with datetime and integer fields.
which series type are you using ?
Using a Line series I'm able to see the gaps where there are null values using your code with a database with datetime and integer fields.
Pep Jorge
http://support.steema.com
http://support.steema.com
We are using a line.
Here is the Code if that helps
<!--METADATA NAME="TeeChart Pro v5 ActiveX Control" TYPE="TypeLib" UUID="{B6C10482-FB89-11D4-93C9-006008A7EED4}"-->
<!-- #include fileconnect.inc" -->
<%
response.buffer=true
dim h, w
h=request("h")
w=request("w")
'test for emply values
if w=0 then
h=300
w=600
end if
ChartType=Request("Chart")
if ChartType="" then
ChartType=0
end if
Response.BinaryWrite(RunChart)
Function RunChart()
dim img
dim Chart
dim MyVar
'Create Chart
Set Chart = CreateObject("TeeChart.TChart")
'Setup Series
Chart.AddSeries(ChartType)
Chart.Series(0).Marks.Visible=False
Chart.Series(0).Color=vbYellow
'Chart.Series(0).HorizontalAxis=2
Chart.Series(0).Name="Level"
Chart.Series(0).XValues.DateTime = True
Chart.Walls.Left.Transparent=True
Chart.Walls.Left.Color=RGB(35,70,128)
if ChartType=1 then
'**** Bar Look Edit Area
'Chart.Series(0).asBar.BarWidth=5 'Flat Setting in Pixels
Chart.Series(0).asBar.BarWidthPercent=70 'Percent Setting in Pixels
Chart.Series(0).asBar.BarPen.Visible = true 'this hides the 1 pixle outline
Chart.Series(0).asBar.BarPen.Color = RGB(16,138,214) 'If line is visible this is the color setting
' RGB(34,150,123) MCD Green Color RGB(16,138,214) MCD Blue Color
'**** ----------------------- *******
end if
'Chart appearance
Chart.Legend.Visible=false
Chart.Axis.Bottom.Labels.Angle=(-90)
Chart.Height=h
Chart.Width=w
Chart.Panel.Gradient.Visible=True
Chart.Header.Text(0)="Goundwater Levels for " & Request("wellname") & " from " & request("datestart") & " to " & request("dateend")
Chart.Header.Font.Bold=True
Chart.Header.Font.Height=14
Chart.Axis.Bottom.Title.Caption="Collection Date"
Chart.Axis.Bottom.Title.Font.Bold=True
Chart.Axis.Left.Title.Font.Bold=True
Chart.Aspect.View3D=0
Chart.Panel.Gradient.StartColor= RGB(255,255,255)
Chart.Panel.Gradient.EndColor=RGB(255,255,255)
Chart.Axis.Left.Title.Caption="Groundwater Level"
Set RSt = Server.CreateObject("ADODB.RecordSet")
sql="SELECT MIN([GroundWater Elevation]) as MIN, [date], [TIME] FROM [GroundwaterLevel-Citizen-Based-Monitoring] WHERE UNID = '" & Request("wellname") & "' GROUP BY [Date], [TIME] HAVING [Date] BETWEEN'" & request("datestart") & "'AND'" & request("dateend") & "'" & _
" UNION SELECT MIN([GroundWater Elevation]) as MIN,[date], [TIME] FROM [GroundwaterLevel-MCD-Archived-Data] WHERE UNID ='" & Request("wellname") & "' GROUP BY [Date], [TIME] HAVING [Date] BETWEEN'" & request("datestart") & "'AND'" & request("dateend") & "'" & _
" UNION SELECT MIN([GroundWater Elevation]) as MIN,[date], [TIME] FROM [GroundwaterLevel-MCD-Groundwater-Levels] WHERE UNID ='" & Request("wellname") & "' GROUP BY [Date], [TIME] HAVING [Date] BETWEEN'" & request("datestart") & "'AND'" & request("dateend") & "'" & _
" UNION SELECT MIN([GroundWater Elevation]) as MIN,[date], [TIME] FROM [GroundwaterLevel-ODNR-Observation-Wells] WHERE UNID = '" & Request("wellname") & "' GROUP BY [Date], [TIME] HAVING [Date] BETWEEN'" & request("datestart") & "'AND'" & request("dateend") & "' ORDER BY [date], [Time]"
session("SQLIS") = sql
RSt.open sql,cn,1,1
showLow = 1000
while not rst.eof
if isnull(rst("min")) or rst("min")=0 then
chart.series(0).addnull rst("date")
else
Chart.Series(0).Add rst("min"), rst("date"),RGB(34,150,123)
showLow = rst("min")
end if
rst.movenext
wend
Dim TheMin, TheMax
TheMin = 1072.5 * .02
TheMax = 1077.19 * .02
Chart.Axis.Left.AutomaticMinimum = True
session("Testing") = "X:" & showLow & ":X"
'Cleanup and set Chart to send to browser
Rst.close
Set Rst=nothing
img=Chart.Export.Stream(stJpeg)
Set Chart=nothing
RunChart=img
end function
%>
Here is the Code if that helps
<!--METADATA NAME="TeeChart Pro v5 ActiveX Control" TYPE="TypeLib" UUID="{B6C10482-FB89-11D4-93C9-006008A7EED4}"-->
<!-- #include fileconnect.inc" -->
<%
response.buffer=true
dim h, w
h=request("h")
w=request("w")
'test for emply values
if w=0 then
h=300
w=600
end if
ChartType=Request("Chart")
if ChartType="" then
ChartType=0
end if
Response.BinaryWrite(RunChart)
Function RunChart()
dim img
dim Chart
dim MyVar
'Create Chart
Set Chart = CreateObject("TeeChart.TChart")
'Setup Series
Chart.AddSeries(ChartType)
Chart.Series(0).Marks.Visible=False
Chart.Series(0).Color=vbYellow
'Chart.Series(0).HorizontalAxis=2
Chart.Series(0).Name="Level"
Chart.Series(0).XValues.DateTime = True
Chart.Walls.Left.Transparent=True
Chart.Walls.Left.Color=RGB(35,70,128)
if ChartType=1 then
'**** Bar Look Edit Area
'Chart.Series(0).asBar.BarWidth=5 'Flat Setting in Pixels
Chart.Series(0).asBar.BarWidthPercent=70 'Percent Setting in Pixels
Chart.Series(0).asBar.BarPen.Visible = true 'this hides the 1 pixle outline
Chart.Series(0).asBar.BarPen.Color = RGB(16,138,214) 'If line is visible this is the color setting
' RGB(34,150,123) MCD Green Color RGB(16,138,214) MCD Blue Color
'**** ----------------------- *******
end if
'Chart appearance
Chart.Legend.Visible=false
Chart.Axis.Bottom.Labels.Angle=(-90)
Chart.Height=h
Chart.Width=w
Chart.Panel.Gradient.Visible=True
Chart.Header.Text(0)="Goundwater Levels for " & Request("wellname") & " from " & request("datestart") & " to " & request("dateend")
Chart.Header.Font.Bold=True
Chart.Header.Font.Height=14
Chart.Axis.Bottom.Title.Caption="Collection Date"
Chart.Axis.Bottom.Title.Font.Bold=True
Chart.Axis.Left.Title.Font.Bold=True
Chart.Aspect.View3D=0
Chart.Panel.Gradient.StartColor= RGB(255,255,255)
Chart.Panel.Gradient.EndColor=RGB(255,255,255)
Chart.Axis.Left.Title.Caption="Groundwater Level"
Set RSt = Server.CreateObject("ADODB.RecordSet")
sql="SELECT MIN([GroundWater Elevation]) as MIN, [date], [TIME] FROM [GroundwaterLevel-Citizen-Based-Monitoring] WHERE UNID = '" & Request("wellname") & "' GROUP BY [Date], [TIME] HAVING [Date] BETWEEN'" & request("datestart") & "'AND'" & request("dateend") & "'" & _
" UNION SELECT MIN([GroundWater Elevation]) as MIN,[date], [TIME] FROM [GroundwaterLevel-MCD-Archived-Data] WHERE UNID ='" & Request("wellname") & "' GROUP BY [Date], [TIME] HAVING [Date] BETWEEN'" & request("datestart") & "'AND'" & request("dateend") & "'" & _
" UNION SELECT MIN([GroundWater Elevation]) as MIN,[date], [TIME] FROM [GroundwaterLevel-MCD-Groundwater-Levels] WHERE UNID ='" & Request("wellname") & "' GROUP BY [Date], [TIME] HAVING [Date] BETWEEN'" & request("datestart") & "'AND'" & request("dateend") & "'" & _
" UNION SELECT MIN([GroundWater Elevation]) as MIN,[date], [TIME] FROM [GroundwaterLevel-ODNR-Observation-Wells] WHERE UNID = '" & Request("wellname") & "' GROUP BY [Date], [TIME] HAVING [Date] BETWEEN'" & request("datestart") & "'AND'" & request("dateend") & "' ORDER BY [date], [Time]"
session("SQLIS") = sql
RSt.open sql,cn,1,1
showLow = 1000
while not rst.eof
if isnull(rst("min")) or rst("min")=0 then
chart.series(0).addnull rst("date")
else
Chart.Series(0).Add rst("min"), rst("date"),RGB(34,150,123)
showLow = rst("min")
end if
rst.movenext
wend
Dim TheMin, TheMax
TheMin = 1072.5 * .02
TheMax = 1077.19 * .02
Chart.Axis.Left.AutomaticMinimum = True
session("Testing") = "X:" & showLow & ":X"
'Cleanup and set Chart to send to browser
Rst.close
Set Rst=nothing
img=Chart.Export.Stream(stJpeg)
Set Chart=nothing
RunChart=img
end function
%>
-
- Site Admin
- Posts: 1349
- Joined: Thu Jan 01, 1970 12:00 am
- Location: Riudellots de la Selva, Catalonia
- Contact:
Hi --
Rather than using:
have you tried using:
Rather than using:
Code: Select all
if isnull(rst("min")) or rst("min")=0 then
chart.series(0).addnull rst("date")
else
Chart.Series(0).Add rst("min"), rst("date"),RGB(34,150,123)
Code: Select all
if isnull(rst("min")) or rst("min")=0 then
chart.series(0).addnullXY rst("date"), 0, ""
else
Chart.Series(0).AddXY rst("min"), rst("date"),"",RGB(34,150,123)
Thank you!
Christopher Ireland (Steema crew)
Please be aware of the newsgroup archives:
http://www.teechart.net/support/search.php
http://groups.google.com
http://codenewsfast.com/
Christopher Ireland (Steema crew)
Please be aware of the newsgroup archives:
http://www.teechart.net/support/search.php
http://groups.google.com
http://codenewsfast.com/
That makes the graph change so it looks correct I think.... however the Data Left vertcal data is ranging from 37,500 to like 33,000 and what is not right is the data points only range from 626 to 608 and the date range 8/30/1901 to 9/17/1901 and the actual date range is 4/26/1990 to 9/1/2003 so something has caused the data and the date to mal form. I checked the SQL and it is fine bringing up the correct dates and values. Any suggestions?
-
- Site Admin
- Posts: 1349
- Joined: Thu Jan 01, 1970 12:00 am
- Location: Riudellots de la Selva, Catalonia
- Contact:
Hi --
This code is being run with Regional Options being set to US format.
Does this code work OK for you?
Mmm .. the following code seems to work OK here:I checked the SQL and it is fine bringing up the correct dates and values. Any suggestions?
Code: Select all
Private Sub Form_Load()
Dim i, count
With TChart1
.AddSeries scLine
For i = DateValue("4/26/1990") To DateValue("9/1/2003")
count = count + 1
.Series(0).AddXY count, i, "", clTeeColor
Next i
.Series(0).YValues.DateTime = True
End With
End Sub
Does this code work OK for you?
Thank you!
Christopher Ireland (Steema crew)
Please be aware of the newsgroup archives:
http://www.teechart.net/support/search.php
http://groups.google.com
http://codenewsfast.com/
Christopher Ireland (Steema crew)
Please be aware of the newsgroup archives:
http://www.teechart.net/support/search.php
http://groups.google.com
http://codenewsfast.com/
I found the problem
if isnull(rst("min")) or rst("min")=0 then ' there is a null value here, we should not show it
chart.series(0).addxy rst("date"),rst("min"),"",RGB(255,255,255)
else
chart.series(0).addxy rst("date"),rst("min"),"",RGB(34,150,123)
showLow = rst("min")
Lastdate = rst("date")
end if
This one works however it does not seem to creat a line break between missing data.
We creted a IF Then that works but I can not see to find how we can tell TeeChart to drop a Line connecting to records
If DateDiff("D", LastDate, rst("date")) > 30 then ' Detect if there is more than 30 dats since the last record
'Now wee need to sever the line from the previous record to this ne record
End if
If we can get this fixed then everything is ok Also we should be upgrading to 6 with support today so thnks for your help
if isnull(rst("min")) or rst("min")=0 then ' there is a null value here, we should not show it
chart.series(0).addxy rst("date"),rst("min"),"",RGB(255,255,255)
else
chart.series(0).addxy rst("date"),rst("min"),"",RGB(34,150,123)
showLow = rst("min")
Lastdate = rst("date")
end if
This one works however it does not seem to creat a line break between missing data.
We creted a IF Then that works but I can not see to find how we can tell TeeChart to drop a Line connecting to records
If DateDiff("D", LastDate, rst("date")) > 30 then ' Detect if there is more than 30 dats since the last record
'Now wee need to sever the line from the previous record to this ne record
End if
If we can get this fixed then everything is ok Also we should be upgrading to 6 with support today so thnks for your help
-
- Site Admin
- Posts: 1349
- Joined: Thu Jan 01, 1970 12:00 am
- Location: Riudellots de la Selva, Catalonia
- Contact:
Hi --
Shouldn't this:
be using AddNullXY, e.g.
??
Shouldn't this:
Code: Select all
if isnull(rst("min")) or rst("min")=0 then ' there is a null value here, we should not show it
chart.series(0).addxy rst("date"),rst("min"),"",RGB(255,255,255)
Code: Select all
if isnull(rst("min")) or rst("min")=0 then ' there is a null value here, we should not show it
chart.series(0).addnullxy rst("date"),rst("min"),""
Thank you!
Christopher Ireland (Steema crew)
Please be aware of the newsgroup archives:
http://www.teechart.net/support/search.php
http://groups.google.com
http://codenewsfast.com/
Christopher Ireland (Steema crew)
Please be aware of the newsgroup archives:
http://www.teechart.net/support/search.php
http://groups.google.com
http://codenewsfast.com/
Thanks for you continued support with this. We had another coder fix the problem and here is our Solution .. hopefully this will help others
' FINDING THE FIRST DATA AND DATE
rst.movefirst
dim PreviousDate, PreviousLevel ' These variables hold the last date and observed Level
while isnull(rst("min")) or rst("min")=0 ' This loop finds the first valid date and Level at the query result
PreviousDate=rst("date")
PreviousLevel=rst("min")
rst.movenext
wend
' GRAPH STARTS HERE
while not rst.eof
if isnull(rst("min")) or rst("min")=0 then 'there is a date but not a valid rst("min")
chart.series(0).addxy rst("date"),PreviousLevel,"",RGB(255,255,255) ' draw it white
else
if DateDiff("d",PreviousDate,rst("date")) > 30 then ' date difference between sequential data is more than 30 days there must be a gap
chart.series(0).addxy (rst("date")-0.25),rst("min"),"",RGB(255,255,255) 'if there is a gap between dates, draw white line
chart.series(0).addxy (rst("date")+0.25),rst("min"),"",RGB(34,150,123) 'attached to the white line draw a line for half a day
else ' if dates come one after another draw the line
chart.series(0).addxy rst("date"),rst("min"),"",RGB(34,150,123) '
end if
PreviousLevel=rst("min") ' getting last observed Level
showLow = rst("min")
end if
PreviousDate=rst("date") ' getting last date
rst.movenext
wend
' FINDING THE FIRST DATA AND DATE
rst.movefirst
dim PreviousDate, PreviousLevel ' These variables hold the last date and observed Level
while isnull(rst("min")) or rst("min")=0 ' This loop finds the first valid date and Level at the query result
PreviousDate=rst("date")
PreviousLevel=rst("min")
rst.movenext
wend
' GRAPH STARTS HERE
while not rst.eof
if isnull(rst("min")) or rst("min")=0 then 'there is a date but not a valid rst("min")
chart.series(0).addxy rst("date"),PreviousLevel,"",RGB(255,255,255) ' draw it white
else
if DateDiff("d",PreviousDate,rst("date")) > 30 then ' date difference between sequential data is more than 30 days there must be a gap
chart.series(0).addxy (rst("date")-0.25),rst("min"),"",RGB(255,255,255) 'if there is a gap between dates, draw white line
chart.series(0).addxy (rst("date")+0.25),rst("min"),"",RGB(34,150,123) 'attached to the white line draw a line for half a day
else ' if dates come one after another draw the line
chart.series(0).addxy rst("date"),rst("min"),"",RGB(34,150,123) '
end if
PreviousLevel=rst("min") ' getting last observed Level
showLow = rst("min")
end if
PreviousDate=rst("date") ' getting last date
rst.movenext
wend