: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?