How do I use relative time values for reports?
Analytics, All versions
Oracle B2C Service
When working with reports, you can include filters for date and time fields as selection criteria. For example, with incidents, you can include a filter based on when the incident was created or when the incident was solved.
When you add a date and time field as a filter in a report, you will often select Between as the operator so that you can specify a range of dates to include in your report. Note that if you select "=" you will be selecting a specific date AND a time down to the second, not just a specific date. As a result, it is unlikely that you will use the "=" operator with date and time fields.
When configuring date ranges, you can use either an absolute date or a relative date. If you run a report that uses an absolute date applied to it, the report will always show data for that particular date range, regardless of when you generate the report. If you use relative dates (by enabling the Relative check box), then the dates included in the report are determined relative to the date and time that you generate the report.
Note: When working with relative dates, the 0 option refers to the current time unit, such as the current hour, day, week, or month.
Rounding: In addition, when working with relative dates, you can round to the beginning of the time unit to indicate that the relative time is at the beginning of the hour, day, week, or month. Rounding is useful when you need to include records for a complete day or week, such as all of yesterday's incidents or last week's published answers.
The following image illustrates setting a relative and rounded date range for answers created in the previous month:
Click to enlarge image
Common Examples of Date Ranges
The examples below use intervals where the Relative box is enabled for each date element. For the elements listed, (rounded) means to select the option with the arrow and (NOT rounded) means select the option without the arrow.
All records from Today: Set the date range to be: FROM 0 Days (rounded) TO 0 Days (NOT rounded). That is, enable the relative check boxes and select 0 Days (with the up arrow) to 0 Days (without arrow).
Records for the Last 7 Days: Set the date range to be: FROM - 1 Week (NOT rounded) TO 0 Days (NOT rounded). This will return all records with the date value being up to 7 days before the date that the report is generated. For example, if today is Tuesday the 15th, at 10 a.m., this range would return incidents updated starting from 12:01 a.m. on Tuesday the 8th, up to the current day and time.
Records for the current WEEK: Set the date range to be: FROM - 0 Week (rounded) TO 0 Days (NOT rounded). This will return all records with the date value being in the current week. For example, if today is the Tuesday, this range will return records starting from 12:01 a.m. on the previous Sunday, up to the current day and time.
Records for the previous WEEK Set the date range to be: FROM - 1 Week (rounded) TO 0 Week (rounded). This will return all records with the date value being between Sunday through Saturday of the previous week. For example, if today is Tuesday, the 15th, this will return records starting from 12:01 a.m. on Sunday the 6th, up to midnight on Saturday the 12th.
Records for the previous and current WEEKS Set the date range to be: FROM - 1 Week (rounded) TO 0 Week (NOT rounded): Returns all records with the date value being in the previous and current weeks. For example, if today is Tuesday the 15th, this would return incidents updated starting from 12:01 AM on Sunday the 6th, up to the current day and time.
These examples can help you set the correct ranges for the current or previous hours, day, months, and quarters as well since the only difference would be the time unit you select.
For additional information, refer to the 'Select a relative date range in a report run-time filter' section in online documentation for the version your site is currently running. To access Oracle B2C Service manuals and documentation online, refer to the Documentation for Oracle B2C Service Products.