Changing Dates in Multiple Queries

If you are build a report that includes multiple queries and you want to be able to change the dates of all the queries at once, there are a couple of ways you can get this done — with a little pre-planning, or a little hacking.

The Pre-Planned Approach

Each of the query wizards allows you to select a worksheet cell for the start and end dates of the query. Simply assign two fields to be you start and end date for the queries, then use cell references for the dates in each of your queries. Change the dates in the two fields, refresh all the queries!

Note that the values in those fields can actually be the results of another query — I typically use a Google Analytics query, with Date and Users fields (adding the Users metric forces a 0 row even if there is no traffic on that day). On the Sort/Count tab, I sort by Date ascending with a Max results of 1 to get the top date in the range, then I copy that query for the other worksheet cell and change the sort to descending to get the end date.

Doing this allows me to automate the report with an option to do a manual override (using Refresh Selected Queries to avoid running the date queries again).

After The Fact Hacking Approach

If your report is already built and running, but you need to do a bulk retrofit with different date ranges, you can hack the ‘ᴁ Analytics Edge Queries’ worksheet. On the Edit Mode button, select the dropdown menu item to Show Query Sheet, then look for the ‘ᴁ Analytics Edge Queries’ worksheet tab at the bottom.

The first column lists the parameters of the queries and functions, and each subsequent column is a query. I have included a sample below, with the date rows for the three queries highighted for you.

In order to change the dates for any of the queries, simply ‘hack’ the worksheet and enter new dates in the appropriate cells. Note that the cells are formatted as Text, so you need to enter dates in the string format like 2020-01-31. Alternatively, you could enter a worksheet cell reference like shown the the example above.