Max Vs Sequential Previous Data

[QlikAngel]

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

[tresesco]

One Max row in your data -

N21

XYZ

2/3/16 12:00

500

but you expect 300 as max price, how?

[QlikAngel]

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	-

[QlikAngel]

you correct.. i have given correct output

[QlikAngel]

Amit Biswas i am not able to see your reply..

[tresesco]

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)

[QlikAngel]

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

[tresesco · Accepted solution]

Try like:

=FirstSortedValue(Aggr(SUM( QTY * UNIT_PRICE) + SUM(TAX_AMOUNT),Org, ItemCode, [PO Date]), -[PO Date])

[QlikAngel]

iTS WORKING BUT FOR SOME OF RECORDS VALUES ARE COMING & FOR SOME ITS NOT..

wHAT WILL BE THE CAUSE?

[tresesco]

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.

[QlikAngel]

Thank u so much .. its working perfectly