Steema Central

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 ?

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

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.

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


%>

Hi --

Rather than using:
have you tried using:

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?

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:
This code is being run with Regional Options being set to US format.

Does this code work OK for you?

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

Hi --

Shouldn't this:
be using AddNullXY, e.g.
??

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