Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Please find the attached Sample Data & Output .
I want Org & Itemwise - Max Date of Purchased Price & sequentially Previous Purchase Price for the same Item & Org.
I am getting Max Purchased Price but Previous Purchase price not getting correct.
Please help
Regards,
Mayuri
One Max row in your data -
N21 | XYZ | 2/3/16 12:00 | 500 |
but you expect 300 as max price, how?
OutPut will be below
Org | ItemCode | Max Date | Previous Date | Max Price | Previous Price | |
N21 | XYZ | 2/3/2016 12:00 | 1/3/2016 17:00:00 PM | 500 | 100 | |
F24 | ABC | 1/3/2016 17:00:00 PM | 1/3/2016 10:00 | 200 | 100 | |
F24 | XYZ | 2/3/2016 12:00 | - | 200 | - |
you correct.. i have given correct output
Amit Biswas i am not able to see your reply..
Straight table:
Dim1: Org
Dim2: ItemCode
Max Date : = Date(Max([PO Date]))
Prev Date : =Date(Max([PO Date] ,2 ))
Max Price : =FirstSortedValue([Unit Price], -[PO Date])
Prev Price : =FirstSortedValue([Unit Price], -[PO Date] ,2)
HI,
I HAVE TRIED THIS WITH LIVE DATA..
DATE IS WORKING PERFECTLY
BUT FOR PRICE THERE IS A FORMULA LIKE BELOW
SUM( QTY * UNIT_PRICE) + SUM(TAX_AMOUNT)
HOW IT WILL WORK WITH FIRST SORTED VALUE
AS I HAVE TRIED BUT IT SHOWS ERROR IN EXPRESSION.
SUGGESTIONS ARE WELCOME
Try like:
=FirstSortedValue(Aggr(SUM( QTY * UNIT_PRICE) + SUM(TAX_AMOUNT),Org, ItemCode, [PO Date]), -[PO Date])
iTS WORKING BUT FOR SOME OF RECORDS VALUES ARE COMING & FOR SOME ITS NOT..
wHAT WILL BE THE CAUSE?
May be there are cases where max dates have same max value more than once. If so, try with distinct like:
FirstSortedValue( Distinct Aggr(SUM( QTY * UNIT_PRICE) + SUM(TAX_AMOUNT),Org, ItemCode, [PO Date]), -[PO Date])
Note: you would get syntax error, just ignore that.
Thank u so much .. its working perfectly