In Google Sheets, take the first and last value of a query to summarize, grouped by user and shorted by date

Question

:Edit -> added a working spreadsheet with the data https://docs.google.com/spreadsheets/d/1RNlZtvYZIpRF97u-7cOEGLS4my4OcLHD_1OJfDRCVCA/edit#gid=2100307022

---

I have a spreadsheet with a list of users, a value, and a date:

And I would like to take the first and the last value of column B grouping by column A (the user) and shorted by column C (the date of the value) and subtract this values

John 42-106 (-64)

Arthur 47-113 (-66)

Ben 53-48 (5)

Daniel 125-125 (0)

Richard 46-121 (-75)

I'm new with the query and the most I found is a sum query, grouped correctly, but shorted by the value, not the date

=query(A:C,"Select A,B, sum(B) group by A,B")

I tried to add the order by column C argument

=query(A:C,"Select A,B, sum(B) group by A,B order by C")

but it didn't work. #VALUE!

and, of course, I don't want a sum, I need a subtract

I can make a external cell with the subtract if the query only respond with the values in different columns (John | 42 | 106), but I need that the data became shorted by the date like (Ben | 53 | 48)...

What can I do?

Answer 1

In your sample data, the first and last values are also the smallest and largest per name. If that is the case with your real data as well, you can get those values like this:

=query(A1:C, "select A, min(B), max(B) where A is not null group by A", 1)

In your sample spreadsheet, the first and last values are also the earliest and latest by the date in column C. These formulas will give results that match the expected results you show:

=query(A1:C, "select A, toDate(min(C)), toDate(max(C)) where A is not null group by A", 0)

=arrayformula( if( isnumber(K3:K) * (K3:K = J3:J), 0, iferror( vlookup($I3:$I & J3:J, { $A1:$A & int($C1:$C), $B1:$B }, 2, false) ) ) )

=arrayformula( iferror( vlookup($I3:$I & K3:K, { $A1:$A & int($C1:$C), $B1:$B }, 2, false) ) )

=arrayformula( if( M3:M + L3:L, M3:M - L3:L, iferror(1/0) ) )