Graphing similar data from multiple sources

joey_lutes — Thu, 18 Aug 2016 15:42:51 GMT

Hello,

I have a bit of a challenging dilemma I'd like to throw out there.

I have a report that is created weekly that generates a variety of data, one element of which I have particular interest.

A) Each report has a specific date associated with it.

B) Each report shows forecast data, monthly, for 24 months.

Over a 6-month timeframe (for example), I'll have had 24 reports - each showing actual 'sales' and a 24-month forecast of sales based on a trending algorithm.

What I'm looking for is a graph that shows 3 trend lines:

1) Actual sales (historical only)

2) Current forecast (future only, obviously)

3) forecast from date X - a line that showed the forecast as of date x (user-selected)

I have built already a forecast calendar. I'm assuming I'd need to build one as well for ReportDate.

(note: this data has multiple dates, I have a calendar for each of them as well as a datebridge and canonical calendar)

From a data modeling perspective, I would suspect that each report that comes in, there would need to be a crosstable that has the following fields for each 'Product':

Product ReportDate ForecastMonth ForecastSalesData

Today, from 1 report, that crosstable has 1M rows. Obviously as time goes on, we're looking at adding 1M rows to that each week. Assuming we maintain 2 years of data, that's >100M rows. Do we think that would be a problem?

I'm wondering what the expressions might look like within that line graph or what else I might be missing?

Thanks in advance!

topic Graphing similar data from multiple sources in App Development

Graphing similar data from multiple sources