Google-sheets – Query specific values based on date ranges

google sheetsgoogle-sheets-query

Note: This doesn't have to be a query. Whatever works best!

I have the following data (anonymized) set in a Worksheet (File Upload Data)

A           B         C              D        E          
Date        Time      Tenant         Client   Client ID  
5/30/2017   9:13:35   client id 0    Test 0   id value 0  
5/29/2017   13:05:44  client id 1    Text 1   id value 1  
5/29/2017   13:14:53  client id 1    Text 1   id value 1  
5/29/2017   13:27:13  client id 1    Text 1   id value 1 
5/29/2017   16:57:43  client id 2    Text 2   id value 2

Where I'd like to, in another worksheet (in the same spreadsheet file) count the # of "Client ID" (E) values (unique) but only where the date is within ranges (yesterday, last 7 days, last 14 days, last 30 days, last 60 days)

I can't figure this out for the life of me. I keep getting hit with:

Unable to parse query string for Function QUERY parameter 2: ADD_COL_TO_GROUP_BY_OR_AGG: C

or some variance of an error message.

If the date ranges needs to be accomplished by hard-coding the values in, I can do that by spreading it across the columns. But, I have a bunch of columns that has data in this 'style', and once this is knocked down, I'd be able to replicate it across the rest of the spreadsheet.

Queries were as follows:

First I tried:

=QUERY('Sheet1'!A2:E, "SELECT C, SUM(E) WHERE C = 'client id 1'").

This got me the error message above.

Then I tried

=QUERY('Sheet1'!A2:E, "SELECT 'Sheet1'!C2:E SUM('Sheet1'!E)")

That got me

Unable to parse query string for Function QUERY parameter 2: PARSE_ERROR: Encountered "!"

Then I did:

=QUERY(
    'Sheet1'!A2:E, 
            "SELECT C,
            WHERE C = A2 AND WHERE A = 5/30/2017
            " 
)")

That got me:

Unable to parse query string for Function QUERY parameter 2: PARSE_ERROR: Encountered "WHERE" at line 2, column 17. Was expecting one of: "true"

Ad nauseam on attempts from there.

Best Answer

Grouping

In general, aggregation functions such as SUM or COUNT are used in the presence of GROUP BY: one specifies how to group the rows so that summation or counting is executed within each group. You don't have any GROUP BY. It would make sense to group by E, so that you count each client id once.

Date format

When dates are used in a query, they have to appear like date 'yyyy-MM-dd', for example 

where A >= date '2017-05-01' and A <= date '2017-05-21'

Creating such a string from dynamic dates (e.g., 60 days ago, =TODAY()-60 involves conversion to text using text and an annoying amount of concatenation: 

"... where A >= date '" & text(today()-90, "yyyy-MM-dd") & "' and ..."

If you go this route, it is advisable to form the query string in its own cell so you can see what it is. query can take the query string by cell reference.

Alternative: filter

It's much easier to use filter for the task you described. Here is the count of unique values in E2:E where A entry is between 30 and 90 days ago:

=countunique(iferror(filter(E2:E, A2:A >= today()-90, A2:A <= today()-30)))

The wrapper iferror is included to suppress "#N/A" that filter returns when there are no matches. (As pointed out by ocWavean).

Related Topic