Sum where time is maximum for a group of values

[sifatnabil]

Hi,

I have a raw database table like the below, where there are excess number of rows which I want to filter out:

primarykey	market_id	msg_date_time	username	trade_id	completed_amount
1	6	10/11/2014 07:50	aa	0	0
2	5	10/11/2014 07:50	bb	0	0
3	7	10/11/2014 07:56	aa	67	10
4	7	10/11/2014 07:56	aa	67	10
5	7	10/11/2014 08:21	aa	67	10
6	7	10/11/2014 08:23	aa	67	10
7	3	10/11/2014 08:36	ss	8	5
8	63	10/11/2014 08:36	gg	0	0
9	3	10/11/2014 08:41	aa	0	0

For trade_id=67, I want to sum only the row where msg_date_time is maximum. How can I do this in charts and text boxes?

[SergeyMak]

Hi Sifat,

Try to use firstsortedvalue().

Sergey

[sifatnabil]

Thanks Sergey, how exactly would you go about it?

I tried something like this but it doesn't work:

=FirstSortedValue(completed_amount,trade_id)

[SergeyMak · Accepted solution]

Sifat,

PFA

I converted your diatomite to timestamp, ordered your table and used new Id to use FirstSortedValue.

The expression is

sum(Aggr(FirstSortedValue (completed_amount,Id),trade_id,username))

Regards,

Sergey

[#5]

try like

sum(aggr(firstsortedvalue(completed_id,-trade_id),username))

put  -trade_id it gives max values