1 Reply Latest reply: May 7, 2013 7:29 AM by Matthew Bygrave RSS

    Lining up historical data

      Hi all -

       

      New to QV and have spent a while searching; apologies if this has been covered elsewhere.

       

      I have two tables in QV - one with my core Sales metric (a date key and the sales metric); and a second table with date "details" - the date key again, and the actual date, as well as another column that has the "comparable" date.

       

      Example - Date table:

      DateKeyCurrDateComparableDate
      55/1/20115/2/2010
      64/15/20114/16/2010
      75/2/20105/3/2009
      84/16/20104/17/2009

       

      Example - Sales data:

      DateKeySales
      510
      620
      715
      817

       

      I hope that makes sense. Given that I've communicated the data structure appropriately (unfortunately, not something I have much control over), here's where I run into difficulty.

       

      I'd like to have a table (I guess really it's a Straight Table in QV) that has the CurrDate, along with BOTH the corresponding Current Sales (easy to do for me) and ALSO the Sales on the Comparable Date.

       

      For example, one row of my desired straight table would be:

      DateKeyCurrDate
      CurrSalesComparableDateComparableSales
      55/1/2011105/2/200915

       

      It's that last column that I just can't calculate. I have played around with set analysis; trying to have Sales where CurrDate=ComparableDate but I can't get the above result.

       

      Any ideas? Is this possible in QV? Any guidance would be great.

       

      Many thanks,

      QVCanuck

        • Re: Lining up historical data
          Matthew Bygrave

          Hi,

           

          Try using the Aggregate function in your chart expressions.

          You will need two expressions with aggregate functions to give you your desired result.

           

          1st Expression
          Current Sales on CurrDate e.g.
          Aggr
              (
               sum([Sales]),
               CurrDate
              )


          2nd Expression
          Sales on ComparableDate e.g.
          Aggr
              (
               sum([Sales]),
               ComparableDate
              )

          Let me know if this works.
          KR Matt.