BigJ
asked on
Charting missing date values on constant x-axis
I have some dates with some number values, but the dates have gaps in between. For example, January (130) March (250)
August (400).
How can I plot a graph which plots EVERY subsequent date from the first date to the last? In the above example, missed months like February would have a value of nothing or 0.
I'm using Access95. I know Excel can recognise dates on the x-axis and it knows to plot all the dates, but Excel is not an option here.
August (400).
How can I plot a graph which plots EVERY subsequent date from the first date to the last? In the above example, missed months like February would have a value of nothing or 0.
I'm using Access95. I know Excel can recognise dates on the x-axis and it knows to plot all the dates, but Excel is not an option here.
ASKER
Hi Ozphil,
Wow! Nothing fancy like that. Taking my above example, I just want to plot, say, 12 data points (for one year's data) instead of just Jan, March and Aug - even though all the other data points will equal zero.
Wow! Nothing fancy like that. Taking my above example, I just want to plot, say, 12 data points (for one year's data) instead of just Jan, March and Aug - even though all the other data points will equal zero.
1.Create an array of 12 items, and populate array with known x,y values.
2.Scan array. Mark first non-zero y-value (y1) at x1 , then loop until you reach next non-zero y-value(y2) at x2. Then (y2 - y1)/(x2-x1) is your gradient over that interval.
Rescan interval and populate y-values using (x - x1)*(y2-y1)/(x2-x1).
Using the y2 value as the start value of the next interval, repeat the process.
This is a join-the-dots approach. It doesnt give you line of best fit. But it may be all you require.
2.Scan array. Mark first non-zero y-value (y1) at x1 , then loop until you reach next non-zero y-value(y2) at x2. Then (y2 - y1)/(x2-x1) is your gradient over that interval.
Rescan interval and populate y-values using (x - x1)*(y2-y1)/(x2-x1).
Using the y2 value as the start value of the next interval, repeat the process.
This is a join-the-dots approach. It doesnt give you line of best fit. But it may be all you require.
Refinements can be made. eg, y-values should initially be empty, not zero as indicated, and the empty state checked to determine if y has a value (zero is a value too).
ASKER
Some queries and comments:
I don't know how many records will result from the query - could be 0, 200 or anywhere in between. I chose 12 as an example.
How do I put the data into an array?
What do you mean by scan the array, and how do I do this?
How do I get from an array to a chart?
P.S. Is it OK to reject an answer 'til we've clarified points like these?
I don't know how many records will result from the query - could be 0, 200 or anywhere in between. I chose 12 as an example.
How do I put the data into an array?
What do you mean by scan the array, and how do I do this?
How do I get from an array to a chart?
P.S. Is it OK to reject an answer 'til we've clarified points like these?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Excel is not an option. I don't have Graphics server OCX, but I do have MS-Graph 5.0. I don't need a best fit curve, only joining the dots really.
Is it easy to run Graphics server OCX if I'm running Access95?
For the months in between the ones which appear in the query result, will it be easy to give them zero values? I don't want the graph x-axis to skip months.
Is it easy to run Graphics server OCX if I'm running Access95?
For the months in between the ones which appear in the query result, will it be easy to give them zero values? I don't want the graph x-axis to skip months.
I ran Graphics server with Access 2. Generated my array, and supplied value to Graphics OCX. No problems.
MS-Graph is slow and clumsy. It may have improved since i last looked at it. Ill check later.
To create array, you establish begin and end dates from your query, and redim a dynamic array for the number of intervals ( months).
Then you loop through your query recordset, calculate the interval number for each record (simple calc), and assign values to array element. So your left with a 'sparse' array - some values and lots of holes.
This array can be supplied in code to Graphics Server, each x,y point is now known. The known points would be joined by lines by Graphics Server, hopefully and probably it can do this.
With MS-Graph, i had to copy data to clipboard in code, and switch to MS-Graph OLE object and manually paste in data to MSgraph plot points table. It may be different now.
So depending on your requirements for ease of use, finances you can choose one or the other.
Its easy to switch development from one to the other. My code evaluated all cases.
The data array generated is used whatever means you use to display the graph.
So do that first.
Oh, i remember outputing the array to an Access table, which could be linked to MSgraph as datasource, but im not certain of this. Im doing all this from memory.
MS-Graph is slow and clumsy. It may have improved since i last looked at it. Ill check later.
To create array, you establish begin and end dates from your query, and redim a dynamic array for the number of intervals ( months).
Then you loop through your query recordset, calculate the interval number for each record (simple calc), and assign values to array element. So your left with a 'sparse' array - some values and lots of holes.
This array can be supplied in code to Graphics Server, each x,y point is now known. The known points would be joined by lines by Graphics Server, hopefully and probably it can do this.
With MS-Graph, i had to copy data to clipboard in code, and switch to MS-Graph OLE object and manually paste in data to MSgraph plot points table. It may be different now.
So depending on your requirements for ease of use, finances you can choose one or the other.
Its easy to switch development from one to the other. My code evaluated all cases.
The data array generated is used whatever means you use to display the graph.
So do that first.
Oh, i remember outputing the array to an Access table, which could be linked to MSgraph as datasource, but im not certain of this. Im doing all this from memory.
I just had a look at the MS-Graph 5.0 Active X control and it blew me away! I can assign in code all the values to the OCX'x properties, and select type of graph. And it is fast!
Ill come up with the code later.
Forget about Graphics Server.
Ill come up with the code later.
Forget about Graphics Server.
This is more than a help enquiry. It is a full application component development.
I estimate it to be worth 2,500 points in the forthcoming system.
Could you raise the points to 250, as there is a lot of complex programming involved.
The task is 90% complete, generating the array and graph, but with the gaps. MS-Graph doesnt interpolate, I have to program it in.
I have completed the logic of, but will require careful testing and debugging. Its this 10% which is most time consuming.
I estimate it to be worth 2,500 points in the forthcoming system.
Could you raise the points to 250, as there is a lot of complex programming involved.
The task is 90% complete, generating the array and graph, but with the gaps. MS-Graph doesnt interpolate, I have to program it in.
I have completed the logic of, but will require careful testing and debugging. Its this 10% which is most time consuming.
ASKER
Ozphil,
You've already clearly put in a fair bit of time on this. Since we last spoke I have amazed myself by taking care of the missing month values with some VBA code.
I've left the office for the weekend, but when I return on Monday I will have a look and see exactly how you can help me. This will probably be displaying a combination bar and area chart, but I will confirm the details then.
Your work won't go unrewarded, but give me 'til Monday to get back to you, OK?
You've already clearly put in a fair bit of time on this. Since we last spoke I have amazed myself by taking care of the missing month values with some VBA code.
I've left the office for the weekend, but when I return on Monday I will have a look and see exactly how you can help me. This will probably be displaying a combination bar and area chart, but I will confirm the details then.
Your work won't go unrewarded, but give me 'til Monday to get back to you, OK?
ok. see you then.
ASKER
I'm baaaack...
Well, it seems like I've found a (relatively) easy way to fill in the missing months, do a cumulative total, and graph the results. NO arrays, NO MS-Graph Active-X controls.
In case this is any use to you, this is what I did:
- Got the first and last month from a query
- Added a new record to a new table starting from first month, incrementing by one month at a time and stopping at the last month.
- Did an update query to update the above table with the results from months for which there were numeric values
- Ran a select query on the updated table which used a DSUM for cumulative (ie running) total
- Graphed the findings, and edited the appropriate data series to display as line / area format.
Well, it seems like I've found a (relatively) easy way to fill in the missing months, do a cumulative total, and graph the results. NO arrays, NO MS-Graph Active-X controls.
In case this is any use to you, this is what I did:
- Got the first and last month from a query
- Added a new record to a new table starting from first month, incrementing by one month at a time and stopping at the last month.
- Did an update query to update the above table with the results from months for which there were numeric values
- Ran a select query on the updated table which used a DSUM for cumulative (ie running) total
- Graphed the findings, and edited the appropriate data series to display as line / area format.
I did the same with memory arrays to provide maximum flexibilty in providing graphing points.
I used memory arrays to interpolate the missing data. I did not assume cumulative totals would provide the missing data.
So are you happy with the task being performed as youve outlined it?
I used memory arrays to interpolate the missing data. I did not assume cumulative totals would provide the missing data.
So are you happy with the task being performed as youve outlined it?
ASKER
Yes thanks, I am happy with how it's working - it's doing exactly what I needed. I do appreciate your looking into MS-Graphics server and Graph 5.0 ActiveX control.
What are my options with dealing with this question? Can I delete it since I got there eventually under my own steam?
What are my options with dealing with this question? Can I delete it since I got there eventually under my own steam?
Its difficult to delete a question in e/e.
Ive never heard of Microsoft refunding a support fee if the enquirer eventually seeks his won solution.
In many cases it is the dialogue that triggers the ideas and possibilities that eventually result in a solution.
This occurs frequently.
Besides how are you graphing the data - in Excel?
Ive never heard of Microsoft refunding a support fee if the enquirer eventually seeks his won solution.
In many cases it is the dialogue that triggers the ideas and possibilities that eventually result in a solution.
This occurs frequently.
Besides how are you graphing the data - in Excel?
BigJ
Remember the 'oops' in Q8630016134 'Checking that a table exists...' :-)
and remember the freebie in Q863002103 'Table as object identifier..' :-)
This system can be cruel at times as well as wonderful.
Remember the 'oops' in Q8630016134 'Checking that a table exists...' :-)
and remember the freebie in Q863002103 'Table as object identifier..' :-)
This system can be cruel at times as well as wonderful.
I have a set of mathematical routines which i developed after scouring the world and finding nothing.
They determine the best line to fit the know points. The result produced by these routines and Excel's match precisely.
The best-fit routines are complex and their implementation is also another complex task.
Im not sure whether I am willing to release them.
Where would you be plotting them?