Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
Date | Price | Expression Result |
---|---|---|
2/7/2016 | 239.28 | 206.32 |
2/6/2016 | 239.15 | 206.09 |
2/5/2016 | 239.11 | 206.09 |
2/7/2015 | 206.32 | --- |
2/6/2015 | 0 | --- |
2/5/2015 | 206.09 | --- |
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)
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)
Hi! Thank you very much! It's not working.
Hi! Thank you very much! It's now working.
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.