Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mikaelforsberg
Partner - Contributor
Partner - Contributor

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

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

Try it in this way:

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

- Marcus