Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

achakilam1022
Contributor

Finding the date range(Qlik Sense)

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.

Price_data.PNGPurchase_data.PNG

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: Finding the date range(Qlik Sense)

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:

2017-08-20 10_25_23-Qlik Sense Desktop.png

2 Replies
MVP
MVP

Re: Finding the date range(Qlik Sense)

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:

2017-08-20 10_25_23-Qlik Sense Desktop.png

achakilam1022
Contributor

Re: Finding the date range(Qlik Sense)

Hi Petter,

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

Thanks