Getting a value of a field based on latest date in another field

[#1]

Example of waht I am trying to do is similar to find the status (open, close , Blocked....) of the last Order (by date ) for a customer .

so, in order to know the last order for a customer we have to find the max Order date for customer and then fecth the Status for that Date.

How to write that in Expression assuming Customer is Dimension and Order Status is a Dimension and date is the Order Date

[#2]

this is how far I got..............the expression gives correct answer if all the orders for the customerhas the same Status..and it will give Null if different status exists

if(aggr(max(Order_Datetime), Customer),OrderStatus)

[#3]

Hello,

If u have sample data thn that ll be more easy to solve your problem.

Thank You

[john_duffy]

Hello.

I am assuming your input data is something like:
LOAD * INLINE [
CUSTOMER, ORDER_STATUS, ORDER_DATE
CUSTA, X, 2010-04-01
CUSTA, Y, 2010-06-01
CUSTB, X, 2010-01-01
CUSTC, X, 2010-02-01
CUSTC, Y, 2010-04-01
CUSTC, Z, 2010-08-01
] ;

and you want your resulting chart to look like:
Customer Order Status
CustA Y
CustB X
CustC Z

The expression If(MAX(total<CUSTOMER> ORDER_DATE) = MAX(ORDER_DATE),MAX(total<CUSTOMER> ORDER_DATE),null()) will give you the result above if you hide the date field and have Suppress Zero Values selected.

John.

[#5]

the expression you have provided is providing the max OrderDate..actually I am trying to get he Status for that order with Max Order Date

[#6]

please use the Example that John has provided below...it it what I am trying to do

[#7]

please find Example attched .

I am trying to calculate the Last Order Status By customer.

[john_duffy]

Hello.

If you add ORDER_STATUS as a dimension and use the following expression, you should get the results you are looking for.

If(MAX(total<CUSTOMER> ORDER_DATE) = MAX(ORDER_DATE),ORDER_STATUS,null())

You can hide the ORDER_STATUS dimension in the Presentation tab.

Let me know if this gives you the required results.