Google Sheet QUERY to get last result

Question

Example - https://docs.google.com/spreadsheets/d/12yO_19wa1awqyQ24EZ7wKzYLBdAX8JdnSFw0TZXG9CI/edit#gid=1852644092

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?

Answer 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)