2

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.

1

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

  • This works, and thanks for the explanation! I have it working in my data set, but for some reason, when I state (as an additional -- optional -- conditional check) the specific tenant (as specified in column C), it always returns 1 even though 100% it's 0. For instance, =COUNTUNIQUE(FILTER('Sheet1'!E2:E, 'Sheet1'!C2:C = "client id 0", 'Sheet1'!A2:A = today()-1)) which makes it the 30th of May, which is 0 (when actually looking at the raw data), but it's stating 1 :( – ocWavean Jun 1 '17 at 18:36
  • EDIT I included IFERROR(...) in front of FILTER(...) and it returns 0 now! – ocWavean Jun 1 '17 at 18:43
  • Good idea. WIthout Iferror the filter returns #N/A as output when there are no matches, so that makes 1 distinct value. – user135384 Jun 1 '17 at 19:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.