Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
mikaelforsberg
Partner
Partner

Dynamic calculation of returns based on selected dates

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.

DateStockPrice
2018-01-01100.00
2018-01-02101.35
2018-01-0397.40
2018-01-0499.75
.....
2018-04-16135.75
......
2018-09-25127.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):

DateStockPriceCumulative return
2018-01-0397.400%
2018-01-0499.752.41%
2018-01-05100.753.44%
2018-01-0899.251.90%
2018-01-0995.50-1.95%
2018-01-10102.004.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!

1 Solution

Accepted Solutions
marcus_sommer
MVP & Luminary
MVP & Luminary

Try it in this way:

sum(StockPrice)/sum({<Date = {"$(=date(Min(Date), 'YYYY-MM-DD'))"}>} TOTAL StockPrice) - sum(1)

- Marcus

View solution in original post

1 Reply
marcus_sommer
MVP & Luminary
MVP & Luminary

Try it in this way:

sum(StockPrice)/sum({<Date = {"$(=date(Min(Date), 'YYYY-MM-DD'))"}>} TOTAL StockPrice) - sum(1)

- Marcus

View solution in original post