Can you use a QUERY in Google Sheets using OR logic and the =today() formula for a dynamic date in the past?

Question

I am trying to use the QUERY function paired with OR logic and the =today() formula to create a new table from my data set.

The data is on the "DATA" tab and 'QUERY' is on the "QUERY" tab. Sheet

Here is a horrible recreation of my data table input (I can't figure out how to make an inline table with more than two columns...):

Column A (ID)	Column B (Date)
1	12/11/2020
2	1/17/2024
3	11/30/2022
4	11/20/2023

Column C (Score1)	Column D (Score2)
52	60
44	40
50	50
53	60

Column E (Score3)
60
50
30
50

For my QUERY output table, I am only getting ID rows 2 and 3, but I was expecting to get ID row 1 as well.

Here is a screenshot of my data table input:

Here is a screenshot of my QUERY table output:

Here are my Criteria: Score1 >45, OR Score2 >50, OR Score3 > 50, OR Date 18+ Months ago (As of writing this, anything before July 26, 2022 would meet the criteria, but I would like this to be a dynamic date and update on its own to include 18 months from today's date).

I am looking to make a table containing every row that meets at least one of these 4 criteria with no duplicate rows.

Here is my QUERY formula:

=QUERY(DATA!A1:E10, "select A, C, D, E, B where(C < 45 OR D < 50 OR E < 50 OR B > '=today()-548')",1)

The part of the 'QUERY' that isn't working is the OR B > '=today()-548' portion. I am trying to include rows that have a date greater than 18 months ago (548 days) from today's date. I thought this would be the best way since the data in the columns I am looking to include are not mutually exclusive. The first three criteria are showing up in my list 'C < 45 OR D < 50 OR E < 50' but the rows that only meet OR B > '=today()-548' is not showing up.

Any help would be greatly appreciated and I would be happy to clarify further.

Answer 1

You may try:

=QUERY(DATA!A1:E10, "select A, C, D, E, B where (C<45 OR D<50 OR E<50 OR B<date'"&text(today()-548,"yyyy-mm-dd")&"')")

OR

=filter({A:A,C:E,B:B},(C:C<45)+(D:D<50)+(E:E<50)+(B:B<today()-548))