Solved

Charting missing date values on constant x-axis

Posted on 1997-10-14
17
426 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 6
17 Comments
 
LVL 4

Expert Comment

by:ozphil
ID: 1957275
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
ID: 1957276
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
ID: 1957277
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 4

Expert Comment

by:ozphil
ID: 1957278
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
ID: 1957279
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
ID: 1957280
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
ID: 1957281
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
ID: 1957282
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
 
LVL 4

Expert Comment

by:ozphil
ID: 1957283
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
ID: 1957284
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
ID: 1957285
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
ID: 1957286
ok. see you then.
0
 
LVL 2

Author Comment

by:BigJ
ID: 1957287
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
ID: 1957288
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
ID: 1957289
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
ID: 1957290
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
ID: 1957291
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

726 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