Record corresponding to max date

[#1]

hey guys, I'm new to qlikview....this may be very easy for you but I'm totally stuck.

I have a calendar which displays the date. I have a table that contains different prices at different dates for the same product. When a particular "date" is selected on the calendar, I want to display the price of the product corresponding to the maximum date that is less than selected date...

here is an example....[Price, Date] [2.25, 01/01/2007] [3.25, 03/01/2008] [4.25,06/01/2009]....When I select 01/01/2009 in the calendar, the price should be displayed as 3.25

I greatly appreciate your help on this...

[rwunderlich]

A typical way to do this is to use IntervalMatch to generate a row for each product/date combination in the range. For this exact scenario, see the QV Cookbook example "Fill values in a data range using previous values.". This example is just like your price problem. You can download the Cookbook from http://robwunderlich.com/Download.html

It has been pointed out that this solution does not work well for a large number of items, as you will have (items * dates) rows. It may work ok with your dataset. I've been meaning to work up another solution with Set Analysis that does not require IntervalMatch. Perhaps someone will post one here...

-Rob

[johnw]

One way would be to use an intervalmatch() to establish a price for every day. But if we extend your problem to be by timestamp instead of just by date, that is no longer a practical solution, which tells me it probably isn't the best solution.

Attached is another approach, but the expression is kind of ridiculous for such a simple concept, so there almost has to be a better way. But I figured a couple bad solutions might be better than no solution.

=max(aggr(if("Price Date"=date(max(aggr(if("Price Date"<=max("Date"),"Price Date"),"Price Date"))),"Price"),"Price Date"))

[johnw]

And here's a set analysis version. Again, while less ugly, a pretty ugly expression, and I suspect there's a better one still:

max({<Date={'$(=date(max({<Price-={},Date={"<=$(=date(max(Date)))"}>}Date)))'}>}Price)

[#5]

Thank you Rob and John. Although you guys seem to think it's ugly, I'm getting it to work exactly as I wanted.

Thanks very much for your help.