6 Replies Latest reply: Jul 15, 2016 11:16 AM by Haritej Kambhampati

# 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).

Thanks

-Hari

• ###### Re: Last but One Date/Price based on Condition

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.

• ###### Re: Last but One Date/Price based on Condition

Hi Nicole,

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

Good Day

Hari

• ###### Re: Last but One Date/Price based on Condition

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

• ###### Re: Last but One Date/Price based on Condition

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

• ###### Re: Last but One Date/Price based on Condition

Or may be this:

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

• ###### Re: Last but One Date/Price based on Condition

Hi Sunny,

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).