Add-in: 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 be manually entered, contain a formula based on TODAY(), or be the results of another query.

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