Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Last but One Date/Price based on Condition

Hello Guys,

I am trying to find out the most recent price changes on Materials. Here is what I have in the table

Material      Valid From Date   Valid To Date    Price

123               01/01/2012          01/06/2012        10

123               01/07/2012          05/12/2013         20

123               06/12/2013          12/31/9999         30

As shown above, the Valid to date of the Last change is always set to 12/31/9999 which helps me get the price changed to (30 In this case). I am not sure how to the the price before last change (20 in this case).

Appreciate your help

Thanks

-Hari

1 Solution

Accepted Solutions
Nicole-Smith

This should do the trick:

FirstSortedValue(Price, -[Valid To Date],  2)

FirstSortedValue sorts the Price field by [Valid To Date] (descending because of the - sign), and then takes the second value because of the 2 in the third parameter.

View solution in original post

6 Replies
Nicole-Smith

This should do the trick:

FirstSortedValue(Price, -[Valid To Date],  2)

FirstSortedValue sorts the Price field by [Valid To Date] (descending because of the - sign), and then takes the second value because of the 2 in the third parameter.

sunny_talwar

Or may be this:

FirstSortedValue({<[Valid To Date] -= {'12/31/9999'}>}Price, -[Valid To Date])

Not applicable
Author

Hi Nicole,

The solution worked like a charm. Appreciate you taking time to explain the function features below.

Good Day

Hari

oknotsen
Master III
Master III

If your question is now answered, please flag the Correct Answer (via the big "Correct Answer" button near every post).

If not, please make clear what part of this topic you still need help with .

May you live in interesting times!
Not applicable
Author

Hi Sunny,

Thanks for your quick response.

I am a little confused at the {<[Valid To Date] -= {'12/31/9999'}>}

part of the expression. Does "-=" take the previous value to '12/31/9999' ? or was that just a typo.

Any case I am wondering if the expression just gives out the Price when the Valid to Date is 12/31/9999 as 2 (Rank is not mentioned).

Appreciate your response.

Good Day

Hari

Not applicable
Author

Done. Thanks for letting me know of this feature. I am new to these forums.