Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sifatnabil
Specialist
Specialist

Sum where time is maximum for a group of values

Hi,

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

primarykeymarket_idmsg_date_timeusernametrade_idcompleted_amount
1610/11/2014 07:50aa00
2510/11/2014 07:50bb00
3710/11/2014 07:56aa6710
4710/11/2014 07:56aa6710
5710/11/2014 08:21aa6710
6710/11/2014 08:23aa6710
7310/11/2014 08:36ss85
86310/11/2014 08:36gg00
9310/11/2014 08:41aa00

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?

1 Solution

Accepted Solutions
SergeyMak
Partner Ambassador
Partner Ambassador

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

Regards,
Sergey

View solution in original post

4 Replies
SergeyMak
Partner Ambassador
Partner Ambassador

Hi Sifat,

Try to use firstsortedvalue().

Sergey

Regards,
Sergey
sifatnabil
Specialist
Specialist
Author

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
Partner Ambassador
Partner Ambassador

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

Regards,
Sergey
Not applicable

try like

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

put  -trade_id it gives max values