1 Reply Latest reply on Aug 26, 2012 10:50 PM by Joe Mako

    How to connect and blend data from 2 .xls sheets at different aggregations

      I've got Table 1 and 2 below in a single xls file. The first table has monthly forecast $ by Contract.

      Table 2 has the actual detail by Contract, but daily instead of monthly, and by line number, instead of by contract.

       

      My desired end goal is to link the 2 tabs in Tableau, and where I have a contract that matches, roll up the Table 2 data by Contract/Month, to compare to Table 1, can compute a variance between the 2 Scenarios.

       

      How should I proceed?

       

      Table 1: Forecast by Contract / Month
      Contract #PeriodAmountScenario
      ABCJan 2012100.00Forecast
      DEFJan 2012125.00Forecast
      GHIJan 2012150.00Forecast
      JKLJan 2012115.00Forecast
      etc.etc.etc.etc.
      Table 2: Actual by Contract / Contract Line / Day
      Contract #Contract LineDateAmountScenario
      ABCABC11/3/201214.00Actual
      ABCABC11/6/20128.00Actual
      ABCABC21/3/20126.50Actual
      etc.etc.etc.etc.etc.
      Desired Result
      Contract #Period (Month Year)AmountScenario
      ABCJan 2012100.00Forecast
      ABCJan 2012133.00Actual