Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Not applicable

Top value in expression

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 ?

Tags (1)