0 Replies Latest reply: Aug 18, 2016 11:42 AM by Joey Lutes RSS

    Graphing similar data from multiple sources

    Joey Lutes

      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!