amount for latest date

[#1]

Hi,

I have below data for each Sector , I want to show the Order_Amount which belongs to last Order_Date for each Sector using Straight Table as shown below,

I am able to calculate Max_Order_date by using below formula

Max_Order_Date= Aggr(Max(Order_Date),Sector)

Data:

Sector	Order_Date	Order_Amount
CXHNN	12-Jun-2016	20
CXHNN	13-Jun-2016	70
CXHNN	14-Jun-2016	10
CXHNN	15-Jun-2016	60
CLD0P5	02-Jun-2016	10
CLD0P5	09-Jun-2016	20
CLD0P5	12-Jun-2016	90
CLD0P5	14-Jun-2016	40
CLD0P5	16-Jun-2016	50

Expected Results:

Sector	Max_Order_Date	Order_Amount
CXHNN	15-JUN-2016	60
CLD0P5	16-JUN-2016	50

[swuehl · Accepted solution]

Dimension:

Sector

Expressions:

=Date(Max(Order_Date))

=FirstSortedValue( Order_Amount, -Order_Date)

edit:

or if you need to aggregate your amounts:

=FirstSortedValue( Aggr(Sum(Order_Amount),Sector,Order_Date), -Aggr(Order_Date,Sector,Order_Date) )

[sunny_talwar]

Just to add a small point, make sure that your Date are read properly. If those are not read as date, the solution might fall apart.

For reading the dates correctly read here:

Get the Dates Right

Why don’t my dates work?

[swuehl]

That's true and important, Sunny!

I assumed dates are correctly interpreted, since the OP is already able to use Max(Order_Date).

[sunny_talwar]

Did not suggest your response to be incorrect. Just wanted to point that date is an important component

[#6]

Thanks all

Please suggest if below results display can be possible as well , sample data is same as mentioned in original post

Sector	Order_Date	Last_Order_Amount
CXHNN	12-Jun-16	60
CXHNN	13-Jun-16	60
CXHNN	14-Jun-16	60
CXHNN	15-Jun-16	60
CLD0P5	2-Jun-16	50
CLD0P5	9-Jun-16	50
CLD0P5	12-Jun-16	50
CLD0P5	14-Jun-16	50
CLD0P5	16-Jun-16	50

[swuehl]

Try

2 Dimensions:

Sector

Order_Date

1 Expression:

=FirstSortedValue( TOTAL<Sector> Order_Amount, -Order_Date)

[#8]

Hi swuehl,

It is working like charm!

[#9]

Hi swuehl,

Can you please help suggest how the expression showing expected results after including <Sector> after TOTAL

What I know is if <dimension> is mentioned after TOTAL then expression excludes the <dimension> while performing calculation so based on this above should ignore <Sector> and show the result as 50 based on <Last Order Date> i.e 16-Jun-2016  

[swuehl]

No, the field listed in the TOTAL<Field> list is still considered as dimension, while any other dimension is ignored (Order_Date in your example with a chart with two dimensions Sector and Order_Date).