topic Re: Last but One Date/Price based on Condition in App Development

Last but One Date/Price based on Condition

Thu, 14 Jul 2016 20:33:38 GMT

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

Re: Last but One Date/Price based on Condition

Nicole-Smith — Thu, 14 Jul 2016 21:52:02 GMT

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

sunny_talwar — Thu, 14 Jul 2016 23:47:45 GMT

Or may be this:

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

Re: Last but One Date/Price based on Condition

Fri, 15 Jul 2016 15:01:26 GMT

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

oknotsen — Fri, 15 Jul 2016 15:04:07 GMT

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 .

Re: Last but One Date/Price based on Condition

Fri, 15 Jul 2016 15:10:39 GMT

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

Re: Last but One Date/Price based on Condition

Fri, 15 Jul 2016 15:16:29 GMT

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