Select a Date Range
To change the date range of an existing data block, select Edit a data block or use the QUICK EDIT panel.
Use the following options to change a date range for a data block.
Calendar
The Calendar allows you to create static or rolling dates using the following options:
- Date range field
- Calendar
- Preset drop-down menu
- Rolling date mode
- Customize expressions
From cell
The From cell option allows you to reference dates entered in worksheet cells.
You have the option to exclude today on any selected date range.
Use the Calendar
When you use the Calendar, the date range field displays the current date range for the data block request. You can enter dates directly into the date range field or use a data range selection option.
Date range field
To enter dates directly into the date range field
-
Click the date range field next to the calendar icon.
-
Enter start and end dates for your date range.
Calendar
To select dates using the calendar
-
Click the calendar icon to display a monthly calendar.
-
Click a start date.
-
Click an end date.
To set a date range in reverse, click the end date first and then click the start date.
Preset drop down menu
The preset drop-down menu includes a standard set of preset date ranges and date range components for a data view that you saved or a data view that was shared with you.
Rolling dates
The rolling dates option allows you to select a date range using rolling dates.
-
Select Use rolling dates.
-
Select a rolling expression for your start and or end date.
Start of — Allows you to select the beginning of a day, week, month, quarter, or year.
End of — Allows you to select the end of a day, week, month, quarter, or year.
Fixed day — Allows you to fix a start or end date while the other date is rolling.
-
Choose day, week, month, quarter, or year as the rolling period.
-
Add or subtract days, weeks, months, quarters, or years from your rolling date.
-
Click Next to define the data range.
Use the date preview to confirm the resulting date range is the desired range.
Custom expressions
The custom expression option allows you to change the date range by building a custom expression or you can enter an arithmetic formula.
-
Select Use rolling dates.
-
Select Use custom expression.
When you select the Use custom expression option, the standard rolling date range controls are disabled.
-
Enter a custom expression.
For a sample list of custom expressions, see Date expressions.
-
Use the date preview to verify the resulting date range is the desired range.
Create a custom expression
-
Enter a Date reference.
-
Add Date operators to move the date to the past or future.
You can enter a custom date expression that includes multiple operators, such as tm-11m-1d
.
Date references
The following table lists date reference examples.
Date operators
The following table lists date operator examples.
Date expressions
The following table lists date expression examples.
Date range from cell
The date range can be specified in worksheet cells. Use the Date range from cell option to choose the data block start and end date from selected cells. When you select the From cell option, the panel displays From and To fields where you can enter a cell location.
Exclude today
Choose the Exclude today option to exclude today from a selected date range. Choosing to include today may pull incomplete data for today.
When selected, the Exclude today option excludes the current day from all date range modes including calendar, rolling dates, or custom expressions.
Valid date ranges
The following list describe valid date range formats.
-
The start and end dates must be in the following format: YYYY-MM-DD
-
The start date must be earlier to or equal to the end date. Both dates can be set to the future.
-
When using rolling dates, the start date must be today or in the past. It must be in the past if Exclude today is checked.
-
You can create a static date range set for the future. For example, you may need to set a future date for a marketing campaign launch next week. This option creates a workbook monitoring for a campaign ahead of time.
Change the date range
You can edit the date range of an existing data block by selecting Edit data block in the COMMANDS panel or by selecting the date range link in the QUICK EDIT panel.
Edit data block — Allows you to edit multiple data block parameters, including date range, for a single data block.
Quick Edit: Date range — Allows you to edit the date range of one or more data blocks.
To edit the date range from the QUICK EDIT panel
-
Select cells within one or more data blocks in a worksheet.
-
Click the Date range link in the QUICK EDIT panel.
-
Select the date range using any of the date selection options.
-
Click Apply.
Report Builder applies the new date range to all data blocks in the selection.