We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

# Charting missing date values on constant x-axis

on
Medium Priority
456 Views
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.
Comment
Watch Question

## View Solution Only

Commented:
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?

Commented:
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.

Commented:
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.

Commented:
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).

Commented:

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?
Commented:
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.

Not the solution you were looking for? Getting a personalized solution is easy.

Commented:
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.

Commented:
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.

Commented:
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.

Commented:
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.

Commented:
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?

Commented:
ok. see you then.

Commented:
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.

Commented:
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?

Commented:
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?

Commented:
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?

Commented:
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.

##### Thanks for using Experts Exchange.

• View three pieces of content (articles, solutions, posts, and videos)
• Ask the experts questions (counted toward content limit)
• Customize your dashboard and profile