In most Analytics Edge wizards, you can select a data range for your query. There are a large number of combinations possible, but here are the most common selections.
Use a Preset Value
First, check the preset values available. If there is one that matches what you want, select it.
Start Date / Duration / End Date Combinations
When setting your own date range, you need to specify a combination of:
- start date + end date
- start date + duration
- duration + end date
Note that you cannot select values for all three. With start date + duration for example, the end date will be calculated for you.
Date Options – Specific Date
If you want to select specific, unmoving dates, you can use the date picker control and select start and/or end dates.
This is useful for one-off queries for marketing campaigns with fixed start or end dates, or for tracking behavior before and after a specific change event.
Date Options – Floating References
If you want to select floating references to things like last week or last month, you can pick from the list for the ‘start of’ or ‘end of’ a preset option.
These are the most common references, allowing you to lock the end date to the end of Last Month or Last Week — those references move over time, allowing your report to always refresh with the most recent month or week’s data without changing the query.
Date Options – Value or Cell Reference
You can also use a date that already exists in a worksheet cell by making a cell reference. Cell references can be entered by clicking the button and picking a worksheet cell or by entering the reference manually. In most connectors, the reference must match the worksheet name exactly, with the proper case.
You would use this option for multiple queries in a dashboard report, allowing you to control all the query date ranges from a single pair of cells in the workbook.
Previous Period / Previous Year
Some connectors off the ability to select a date range with a Previous Period or Previous Year option. Selecting these options causes the connector to calculate the date range specified, then to shift it to the previous period (a period of the same length, ending the day before the specified date range) or the previous year (same dates, but one year earlier).
These options are useful for doing comparative queries — so your report can compare the current period to a previous one. The two queries would be identical except for this option selection, so you can make a simple copy and add this option selection.
Common Scenarios
For last week (Sunday – Saturday):
- duration > 1 week
- end date > end of > Last Week
For last week (Monday – Sunday):
- duration > 1 week
- end date > end of > Last ISO Week
For last calendar month:
- duration > 1 month
- end date > end of > Last Month
For last 2 calendar months (useful for month-over-month reporting):
- duration > 2 months
- end date > end of > Last Month
- include a Month of Year dimension in your report
For the past 13 months (useful for month-over-month, year-over-year plus annual trend reporting):
- duration > 13 months
- end date > end of > Last Month
- include a Month of Year dimension in your report
For the last 53 weeks (useful for week-over-week, year-over-year, and annual trend reporting):
- duration > 53 weeks
- end date > end of > Last Week (or Last ISO Week)
- include a Week of Year (or ISO Week of ISO Year) dimension in your report