Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Year on Year Analysis on given Dates

Hi, requesting assistance on the proper expression to be written for a chart, and possibly a line graph. I basically need to compare each date with the stock's price from the exact date 1 year ago. Yet if data for the previous year is not available (or if it is available but appears recorded as 0), then the expression should refer to the stock price of the latest date with available data.

For example, there are 2 columns, Date and Price, with the following sample data and needed expression result. Thanks!

DatePriceExpression Result
2/7/2016239.28206.32
2/6/2016239.15206.09
2/5/2016239.11206.09
2/7/2015206.32---
2/6/20150---
2/5/2015206.09---
1 Solution

Accepted Solutions
swuehl
MVP
MVP

You can do it for example using an As-of Table:

INPUT:
LOAD Date,
     Price
FROM
[https://community.qlik.com/thread/204546]
(html, codepage is 1252, embedded labels, table is @1);

Prices:
LOAD Date, Price, If(Price>0, Date, Peek(LastpriceDate) ) as LastpriceDate
Resident INPUT
ORDER by Date asc;

ASOF:
LOAD Date, Date as AsOfDate, 'Current' as Type
RESIDENT Prices;
LOAD LastpriceDate as Date, Date(AddYears(Date, 1)) as AsOfDate, 'LY' as Type
RESIDENT Prices
Where exists(AsOfDate,Date(AddYears(Date, 1)));

DROP TABLE INPUT;

Then create a table chart with dimension AsOfDate and two expressions:

=Sum({<Type = {Current}>}Price)

=Sum({<Type = {LY}>}Price)

View solution in original post

4 Replies
swuehl
MVP
MVP

You can do it for example using an As-of Table:

INPUT:
LOAD Date,
     Price
FROM
[https://community.qlik.com/thread/204546]
(html, codepage is 1252, embedded labels, table is @1);

Prices:
LOAD Date, Price, If(Price>0, Date, Peek(LastpriceDate) ) as LastpriceDate
Resident INPUT
ORDER by Date asc;

ASOF:
LOAD Date, Date as AsOfDate, 'Current' as Type
RESIDENT Prices;
LOAD LastpriceDate as Date, Date(AddYears(Date, 1)) as AsOfDate, 'LY' as Type
RESIDENT Prices
Where exists(AsOfDate,Date(AddYears(Date, 1)));

DROP TABLE INPUT;

Then create a table chart with dimension AsOfDate and two expressions:

=Sum({<Type = {Current}>}Price)

=Sum({<Type = {LY}>}Price)

Not applicable
Author

Hi! Thank you very much! It's not working.

Not applicable
Author

Hi! Thank you very much! It's now working.

oknotsen
Master III
Master III

If your question is now answered, flag the correct answer with Correct Answer. If not, please make clear what part of your question still needs help.

May you live in interesting times!