Date scaling TeeChart 5 Pro

MCD · Post by **MCD** » Wed Jan 14, 2004 7:51 pm

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.

Post by **Pep** » Fri Jan 16, 2004 9:54 am

Hi,

are you sure you are adding your datetime XValues to the chart as values and not labels ?

MCD · Post by **MCD** » Fri Jan 16, 2004 12:53 pm

We are using this entry:

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)
end if

Chart.Series(0).XValues.DateTime = True

rst.movenext

Thanks
Jason

Post by **Pep** » Mon Jan 19, 2004 11:53 am

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.

MCD · Post by **MCD** » Tue Jan 20, 2004 2:34 pm

We are using a line.

Here is the Code if that helps




<%
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

%>

Post by **Christopher** » Wed Jan 21, 2004 11:13 am

Hi --

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)

have you tried using:

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)

MCD · Post by **MCD** » Wed Jan 21, 2004 5:32 pm

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?

Post by **Christopher** » Thu Jan 22, 2004 12:52 pm

Hi --

I checked the SQL and it is fine bringing up the correct dates and values. Any suggestions?

Mmm .. the following code seems to work OK here:

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

This code is being run with Regional Options being set to US format.

Does this code work OK for you?

MCD · Post by **MCD** » Thu Jan 22, 2004 1:44 pm

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

Post by **Christopher** » Fri Jan 23, 2004 12:28 pm

Hi --

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)

be using AddNullXY, e.g.

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"),""

??

MCD · Post by **MCD** » Fri Jan 23, 2004 1:33 pm

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