last order for day

[#1]

Hi, I would need to get the last total of price by a date and customer, for example

customer	orderid	value	date
x	1	3000	2013/09/26 12:00
x	2	1500	2013/09/26 16:00
y	3	4500	2013/09/27 17:00
y	4	5000	2013/09/29 18:00

so I would need of last value for customer and date, what I would  need:

Customer	Orderid	value	date
x	2	1500	2013/09/26 16:00
y	4	5000	2013/09/29 18:00

I tried  to use the aggr function with max AGGR(max(date),customer) ,for to catch the last date and , I tried to use set analisys,  sum({$<date={AGGR(max(date)}>} Value ) , to catch the value relative to the last date.

someone , can help me?

thanks

[CELAMBARASAN · Accepted solution]

You can do it in two ways one in script and another in chart itself.

Chart:

Dimension: Customer

Expressions:

orderid = FirstSortedValue(orderid, -Num(Date))

value = FirstSortedValue(value, -Num(Date))

Date = TimeStamp(Max(Date))

Script:

Create a flag to identify the last order using any one of the following methods.

Orders:

Load

     customer,

     orderid,

     value,

     date

From source;

Left Join(Orders)

Load

     Customer,

     FirstSortedValue(orderid, -Num(date)) AS orderid,

     1 AS LastOrder

Resident Orders;

Then use expression as Sum({<LastOrder={1}>} value)

Another way is something like

Orders:

Load

     customer,

     orderid,

     value,

     date,

     if(Previous(customer) <> customer and RowNo()>1, 1) AS LastOrder

From source

Order By customer, date;

Then use expression as Sum({<LastOrder={1}>} value)

[#3]

Thank you very very much...

I forgot to said that I needed of aggregation forms, but with your help, I get !

was quite this form

FirstSortedValue(AGGR(SUM([value]),[customer]),-NUM(AGGR(MAX(date),[customer])))

AGGR(MAX(date),[customer])

thank you again !

regards...