CODEWITHSUNDEEP
google-sheetsformulagoogle-sheets-formula
AlexShevyakov
AlexShevyakov

Reputation: 423

Querying data by week range and row value

I have a Google sheet data, arranged as per the screenshot below, where I am trying to query range A1 to C to return records occurred in the previous week (weeknum(now)-1) AND having specific status, i.e. open and closed. I have managed to write a similar query for a single column only, specifically for "B", which works fine:

=ArrayFormula(query({B2:B,if(len(B2:B),weeknum(B2:B),)}," select Col1 where Col2 = "&weeknum(now())&"",1))

enter image description here

What I need, however, is something like this

QUERY(A1:C, "Select B, C, D WHERE B = "&weeknum(now())-1&" "AND (C = 'Open' OR C = 'Closed'"),1)

i.e.

ALL EVENTS FOR PREVIOUS WEEK; WITH STATUS OPEN OR CLOSED

though I know query does not operate with WEEKNUM() and thus the above apparently does not work.

Upvotes: 0

Views: 57

Answers (1)

JPV
JPV

Reputation: 27262

Give this a try and see if it works:

(EDITED)

={B1:D1; ArrayFormula(query({B2:D,if(len(B2:B),weeknum(B2:B),)}, "Select Col1, Col2, Col3 where (Col2 = 'Open' OR Col2 = 'Closed') AND Col4 = "&weeknum(now())-1&"", 0))}

An alternative (and easier) way would be to use filter:

={B1:D1; filter(B:D, (C:C="Open")+(C:C="Closed"), weeknum(B:B)=weeknum(today())-1)}

Upvotes: 1

Related Questions