Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a QlikView question that I have not been able find any posts on in the forums (maybe I've missed something..).
I have a simple Qlikview app with stock prices, and I would like to be able to create a chart that dynamically calculates (and charts) the return of the stock, depending on a chosen start date.
So for example, let's assume that I have the following data.
Date | StockPrice |
---|---|
2018-01-01 | 100.00 |
2018-01-02 | 101.35 |
2018-01-03 | 97.40 |
2018-01-04 | 99.75 |
.. | ... |
2018-04-16 | 135.75 |
... | ... |
2018-09-25 | 127.00 |
I would like to be able to create a chart that shows the cumulative return of the stock. This is easy to do with the help of some scripting, for the whole period, but what I would like to do is to be able to select any date range within the data set and show the return in a graph (for example with the start date of 2018-03-21 until today, and then maybe change the date range to go from 2018-05-01 to 2017-06-31).
Lets say I choose the date range 2018-01-03 to 2018-01-10 then I would like to get the following Cumulative returns (just the stock price for the date divided by the stock price on the first date in the date range minus one, StockPrice(Date) / StockPrice(First date)-1):
Date | StockPrice | Cumulative return |
---|---|---|
2018-01-03 | 97.40 | 0% |
2018-01-04 | 99.75 | 2.41% |
2018-01-05 | 100.75 | 3.44% |
2018-01-08 | 99.25 | 1.90% |
2018-01-09 | 95.50 | -1.95% |
2018-01-10 | 102.00 | 4.72% |
I have not been able to do this using Set Analysis. In a chart, I have tried measures like the following without success (with Dimension Date):
sum(StockPrice)/sum({<Date = {'$(=Min(Date))'}>} StockPrice) - sum(1)
Do you guys have any suggestions how to do this? Thank you in advance!
Try it in this way:
sum(StockPrice)/sum({<Date = {"$(=date(Min(Date), 'YYYY-MM-DD'))"}>} TOTAL StockPrice) - sum(1)
- Marcus
Try it in this way:
sum(StockPrice)/sum({<Date = {"$(=date(Min(Date), 'YYYY-MM-DD'))"}>} TOTAL StockPrice) - sum(1)
- Marcus