CODEWITHSUNDEEP
google-sheetsgoogle-query-language
Inigo
Inigo

Reputation: 8685

using both QUERY and FILTER together in a single statement?

I hope someone can help me; I am building some spreadsheets to help with time-tracking. I have a list of tasks, with columns for criteria including date, hours spent, category of work, and client.

I want to filter this data by month, so for example I would like to know how long I spent in a single month on correspondence. This means I need to select all the rows where category = 'correspondence' and where the dates are all from one specified month. At the moment, I am having to use a query which outputs to an intermediary table, and then run a filter function on that table in order to output to my final table. Here are my two functions:

=QUERY( 'Task List'!A4:F , "select A, B, E, F where C = 'Correspondence'" )

that gives me the first table, with just the rows where the category is "Correspondence". Then, on that table, I have to run the next function:

=filter(J4:M,J4:J>=date(2015,4,1),J4:J<=date(2015,4,31))

To get only the rows from this month of April. If possible I would like to remove the intermediary table (which serves no other purpose and just clutters my sheet).

Is it possible to combine these statements and do the process in one step?

Thanks.

Upvotes: 4

Views: 49221

Answers (2)

Greggory Wiley
Greggory Wiley

Reputation: 961

I came to this question needing to filter by weeknum() and year() as well as query by contains(). It can be helpful to combine the query and filter functions for similar but more dynamic date and text matching needs. If for example the OP had needed to show this data by week, that is not available in the Google Query Language.

The filter function does not have the contains function so you are limited to exact match text or using Reg-Ex. The Query Lanuague does not have the Weeknum functions.

Combining Filter and Query can be useful in scenario similar to this question but with a dynamic timeline (no hard set month or date such as rolling timeline) and where the text your matching is not exact (when you need to use a contains function from query language). Here is an example for combining filter and query in Google sheets. 

=(sum(Filter(QUERY(FB!$A:$Z, "select Q where B contains 'Apple'"), Weeknum (QUERY(FB!$A:$Z, "select E where B contains 'Apple'")) = Weeknum($A8))))

In this example I queried Facebook ads data export for any posts which contained the word 'Apple' in their title, and where Weeknum() matched the ongoing weeks on my sheet, in order to pull weekly data from multiple sources into one table to build reports, with minimal updating required as the timeline runs on.

It selects Q(spend) Where B(title) contains Apple, and Weeknum(E) matches week number on current row of sheet(A8). I have found this useful many times. Query + Filter Example Sheet Here.

If OP wanted to pull this info dynamically as the months went on if A column contained months in order the formula could be pulled along and would automatically pull data from query data filtered by matching month month. 

=(sum(Filter(QUERY( 'Task List'!A:Z , "select A, B, E, F, J where C contains 'Correspondence'" ), Month(QUERY( 'Task List'!A4:F , "select J where C contains 'Correspondence'" )) = Month('$A2'))))

Upvotes: 1

JPV
JPV

Reputation: 27262

That is indeed possible.

Since you didn't specify in which column the dates are to be found (in the 'raw' data), I assumed for this example that dates are in col F. The easiest way would be to use the MONTH() function. However, when used in query(), this function considers January as month 0. That's why I added the +1. See if this works ?

=QUERY( 'Task List'!A4:F , "select A, B, E, F where C = 'Correspondence' and month(F)+1 =4 ")

Upvotes: 3

Related Questions