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 ?