Google Sheet QUERY to get last result

Question

i wanted to get the lowest price in the last 90 days then get the date corresponding to that

fist i tried

=(QUERY(A:D; "SELECT A WHERE C = '"&H4&"' ";0))

with no results, cos from my understanding with referenc its actually looking for a text, not number, so i added TO_TEXT to the data

=ArrayFormula(QUERY(TO_TEXT(A:D); "SELECT Col1 WHERE Col4 = '"&H4&"' ";0))

as it get multiple results

=ArrayFormula(QUERY(TO_TEXT(A:D); "SELECT Col1 WHERE Col4 = '"&H4&"' ORDER BY Col1 DESC LIMIT 1";0))

but as its texts im getting wrong answer 31/07/2020 instead of 08/03/2022

so basicly to solve my first problem i broke sorting as its not values anymore but text

is there a way to handle this making part of data as value and other part as text? or anyother way around it, like not using TO_TEXT in first place?

score 1 · Accepted Answer · answered Apr 08 '22 at 21:04

1

try:

=INDEX(QUERY(A3:D; 
 "select A,min(D) 
  where A >= date '"&TEXT(TODAY()-90; "e-m-d")&"' 
  group by A 
  order by min(D)"); 2; 1)

or just:

=INDEX(SORT(FILTER(A:D; A:A>=TODAY()-90); 4; 1); 1; 1)

answered Apr 08 '22 at 21:04

player0

1

thanks again @player0 that was fast, need to wait more 1 minutes to set as answered, again it was beyound my kwoledge, but im getting it – Bruno Carvalho Apr 08 '22 at 21:09
1

big thanks with both solutions i learnt a bit more, thats even better than just have question answred, – Bruno Carvalho Apr 08 '22 at 21:11
im now trying to get the max of last 90 days from C, adapted formula =INDEX(QUERY(A3:C; "select A, max(C) where A >= date '"&TEXT(TODAY()-90; "e-m-d")&"' group by A order by max(C)"); 2; 1) but im getting same result, no idea why, should i post another question? – Bruno Carvalho Apr 08 '22 at 22:15
1

@BrunoCarvalho should be: https://i.stack.imgur.com/dWXN1.png – player0 Apr 08 '22 at 22:18

1 Answers1