Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
Partner
Partner

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

Hi Sifat,

Try to use firstsortedvalue().

Sergey

Regards,
Sergey
Highlighted
Specialist
Specialist

Thanks Sergey, how exactly would you go about it?

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

=FirstSortedValue(completed_amount,trade_id)

Highlighted
Partner
Partner

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

Highlighted
Not applicable

try like

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

put  -trade_id it gives max values