Finding the date range(Qlik Sense)

[achakilam1022]

Hi all,

I have two data tables; first one contains the price details of the product sold by a company and this price varies with time. There is a recording made in the table when there is a change in the price or a new company is added.

The second table contains the purchase data, i.e, product purchased from which company on which date. I need help in finding the price of the product based on the company it is bought and also check the date to map the price.

Thank you.

[petter · Accepted solution]

If you have the two tables with the same columns as you specify except that the price in table #2 is left out - you can calculate that price in a GUI table with the following expression:

FirstSortedValue( Price , -If(PriceDate<=Date,PriceDate) )

Well to also get the N/A rows you will have to use an If() function also like this:

If(IsNull(FirstSortedValue( Price , -If(PriceDate<=Date,PriceDate) )),'N/A',FirstSortedValue( Price , -If(PriceDate<=Date,PriceDate) ))

Here is how it looks like in Qlik Sense in a sheet in edit mode:

[achakilam1022]

Hi Petter,

Thanks a lot. This expression gives perfect answer. I appreciate your help.

Thanks