topic Re: Get a value in a certain date in QlikView

Get a value in a certain date

Tue, 12 Apr 2016 16:45:52 GMT

In my Qlik application I define two variables: startInvDate and finishInvDate, where the first is the date when an investment starts and the last is the date when it finishes.

I also have some stocks and their AdjClose for each Date, for example:

Date	Equity	Adj Close
2016-04-01	YHOO	36.48
2016-03-31	YHOO	36.81
2016-03-30	YHOO	36.56
2016-03-29	YHOO	36.32
2016-03-28	IBM	148.4
2016-03-29	IBM	149.33
2016-03-30	IBM	148.41
2016-03-31	IBM	151.45

My target is to get the adj Close for finishInvDate variable for each stock. I tried:

=FirstSortedValue([Adj Close],Date)

to get the last Adj Close of a selected Date, but it does not work properly.

Re: Get a value in a certain date

sunny_talwar — Tue, 12 Apr 2016 17:38:54 GMT

What is the expected output from the above sample?

Re: Get a value in a certain date

sunny_talwar — Tue, 12 Apr 2016 17:43:44 GMT

To get the most recent Adj Close, you need to add a negative sign before date:

=FirstSortedValue([Adj Close], -Date)

or this:

=FirstSortedValue({<Date = {"$(='<=' & Date(Max(Date)))"}>}[Adj Close], -Date)