Lining up historical data

[#1]

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:

DateKey	CurrDate	ComparableDate
5	5/1/2011	5/2/2010
6	4/15/2011	4/16/2010
7	5/2/2010	5/3/2009
8	4/16/2010	4/17/2009

Example - Sales data:

DateKey	Sales
5	10
6	20
7	15
8	17

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:

DateKey	CurrDate	CurrSales	ComparableDate	ComparableSales
5	5/1/2011	10	5/2/2009	15

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

[#2]

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.