Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

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
MVP
MVP

Re: Year on Year Analysis on given Dates

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
MVP
MVP

Re: Year on Year Analysis on given Dates

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

Not applicable

Re: Year on Year Analysis on given Dates

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

Not applicable

Re: Year on Year Analysis on given Dates

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

oknotsen
Honored Contributor III

Re: Year on Year Analysis on given Dates

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!