Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
Or may be this:
FirstSortedValue({<[Valid To Date] -= {'12/31/9999'}>}Price, -[Valid To Date])
Hi Nicole,
The solution worked like a charm. Appreciate you taking time to explain the function features below.
Good Day
Hari
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 .
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
Done. Thanks for letting me know of this feature. I am new to these forums.