I’m building a charting component that displays data directly from the raw data records. It should be able to filter data (subset) and then perform some rudimentry maths on the data (sum, avg, etc). It will finally display the data in a month-wise manner with a scrollbar to navigate through months.

I’m currently building this for an offline desktop application scenario, but ideas involving database engines are also appreciated since I could use an offline RDBMS such as SQLite to achieve the same.

An example data set is as follows, and the typical size is 10K to 1M records.

 Cust   Category        Date         Price
{John   Hair products   25-June-2010  25.2}
{Tina   Food products   26-May-2012   635.5}
{Bob,   Food products   4-Apr-2012    35.3}
{Stacy  Hair products   12-Dec-2010   525.2}
(etc)

Assuming a simple business chart displaying “month-wise total sales of hair products”, what are good patterns for computing this data such that it can be displayed in a visual chart?

Some possibilities:

1. Supposing the chart component is displaying only 2012 data, first filter data within the year 2012. Then search for matching records for each month, summing up the total price, to arrive at the month-wise total sales.

2. Pre-separate all data records based on the year, so when we are displaying 2012 data, calculation only needs to be done on data for 2012.

3. Pre-compute all data for all months of all years, the entire data set, possibly displaying a “loading bar” while doing this. Then, when we need to display a certain year’s data, everything is ready for display.

What are you thoughts on this? And what are the usual strategies/algorithms for displaying simple business charts and computing data for charting?