Top value in expression

Wed, 04 Apr 2012 08:12:46 GMT

Hi all, I'm having trouble using the "top" function within an expression.

Here is a description of my problem,

I have a history of product purchase with the date, the price and the product ID.

I also have "typical usage sheet" with a list of product ID.

The goal is to compute the total sheet price for a certain date by looking into the purchase history.

As I don't have a product price for each date, I would like to choose the nearset entry in the history.

I was thinking to order the result by date difference and to take the top one. But I'm not sure how I should do this in QlikView.

I tried using the productID as a dimention in a pivot table and "top(productPrice)" as en expression thinking it would give me each product the first price in the data. But it doesn't work, it returns me a null value for each product.

Any idea ?

Edit : So I guess I should use something lik firstsortedvalue(productPrice, datediff). But the problem is that if I have two price with the same datediff, the function will return null. How could I still have a result ?

topic Top value in expression in QlikView

Top value in expression