Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

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

1 Reply
Not applicable

Re: Lining up historical data

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.

Community Browser