charting field history

We have an object called location with a % complete field that has history tracking turned on.  The history object give us a created date and the new value every time the field is edited.  We created a chart which shows the % complete in the x axis and the location name on the Y axis.  We also created a table showing these values.  It works great in that it shows the last % complete value of each location as a bar from 0% - 100%.

Now what I want to do is filter this this with a start date and end date the user can choose.  The bar would be a stacked series where the first color shows the % complete at the start date and the second color shows the % complete at the end date.

For example if on Monday you were 10% complete and Friday you are 80% the chart show a dark blue bar going to 10% and a light blue bar continuing from 10% to 80%.  Right now I jsut have a single bar going to 80%

How can we get this to work?

I’m not sure you are going to get this seamlessing incoproated into your existing chart. 

But if you are willing to build a new chart here is what you can do. 

1. Create 2 models that retrieve 1 row of field history data.  Each one should have have conditions on the created date. 
2. Build two mechanism to filter on dates and send those dates as values to the model conditions.   One should be for the start date, and the second should be for the end date. 

These mechanisms will probably the trickyest thing about the effort.  I think you should build tables on these two models,  but hide the tables (see this post ).  The date filters should have as thier source a third modle that has a row for ever date change history was captured.   When you select a filter element on these two tables it will update the chart. 

3. Then build the chart with two series. One for the start date and a second for the end date.  You can style the series independently. 

I think you can do what you are looking for!