Solved

Charting missing date values on constant x-axis

Posted on 1997-10-14
17
421 Views
Last Modified: 2008-03-17
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.
0
Comment
Question by:BigJ
  • 11
  • 6
17 Comments
 
LVL 4

Expert Comment

by:ozphil
Comment Utility
Are you looking for the curve of best fit?

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?








0
 
LVL 2

Author Comment

by:BigJ
Comment Utility
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.
0
 
LVL 4

Expert Comment

by:ozphil
Comment Utility
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.




0
 
LVL 4

Expert Comment

by:ozphil
Comment Utility
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).
0
 
LVL 2

Author Comment

by:BigJ
Comment Utility
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?
0
 
LVL 4

Accepted Solution

by:
ozphil earned 150 total points
Comment Utility
Keep the dailogue going with comments if the answer only requires further development.

Populating the array from the query will be the easy bit. Getting it from there to a chart will depend on the graphics display services you have - Excel, MS chart, Graphics Server.

We wont need to do the interpolation calculations with Excel and MS Chart. They do their own best-fit curves for you. Excel is easy to write the data to, MS Chart should be but last time i did it (1994/5) it was not the complete server i would have wished it to be.

Graphics Server OCX is completely programmable and fast, but wont do a best-fit curve for you.

Which of these do you prefer to display your graph on, or do you have something already.







0
 
LVL 2

Author Comment

by:BigJ
Comment Utility
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.
0
 
LVL 4

Expert Comment

by:ozphil
Comment Utility
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.



0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 4

Expert Comment

by:ozphil
Comment Utility
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.


0
 
LVL 4

Expert Comment

by:ozphil
Comment Utility
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.
0
 
LVL 2

Author Comment

by:BigJ
Comment Utility
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?
0
 
LVL 4

Expert Comment

by:ozphil
Comment Utility
ok. see you then.
0
 
LVL 2

Author Comment

by:BigJ
Comment Utility
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.


0
 
LVL 4

Expert Comment

by:ozphil
Comment Utility
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?
0
 
LVL 2

Author Comment

by:BigJ
Comment Utility
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?
0
 
LVL 4

Expert Comment

by:ozphil
Comment Utility
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?





0
 
LVL 4

Expert Comment

by:ozphil
Comment Utility
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.




0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now