You may have some very specific filters that you want to run on a date field in your metric. For example, you may only want to see from the beginning of the month up to yesterday, or you only want to see from the start of the year, up to the end of last month.
This can be accomplished by using Date Filtering in Grow.
Grow has many options when pulling in your data for what dates you want covered. But if you want to get super specific (all days but not today, month to date to yesterday etc) you can use SQL filtering.
In the Advanced SQL box, after the WHERE clause (more info about the where clause here) you can set some parameters on the date range you want to see from your data.
First start with where you want the data to begin. To reference this time period, enter you column name, greater than or less than, and then the date period.
`DateColumn` >= date(‘now’, ['start of month';'start of year';'-2 years’; etc])
There are many time periods you can use. Here is a SQLite time period tutorial.
Click Connect to run this SQL to make sure it is working. Column names need to be wrapped in single tick marks, data in columns need to be wrapped in single quotes. ` is for column names, ' is for data. Notice the tilt of the mark.
If you want a time period that doesn't include today, then you can add your second parameter with an AND statement and then repeat the pattern of column, greater than or less than, date period.
`DateColumn` <= date(‘now’, ['start of month';'start of year';'-2 years’; etc])
Always use 'now' first as your point of reference, so that the data will be dynamic as time goes on.
Click connect to see if the second statement is working. Dates and times can be tricky. Think of the logic behind the date that you want to include and then use the WHERE function to bring in the correct dates. A lot of it will come by trial and error.
Here's another example. I want to pull in my Google Analytics new users in from the start of the month to yesterday.
I would enter the SQL:
select * from report
WHERE `ga:date` >= date('now','start of month')
`ga:date` < date('now')
Questions? Email us at firstname.lastname@example.org or chat in on the site.