Mirror of Oracle documentation
Converted for search and offline reading. Authoritative source: Oracle. Diagrams and some complex tables are simplified — check the PDF when in doubt.
A
Time Series Conversion Functions
There are two types of time conversions, table-based and expression-based:
-
Table-based conversions use a relationship table in the data warehouse to define the conversion from one time period to another.
-
Expression-based conversions perform transformations by using mathematical expressions.
All of the Retail Insights conversions are expression-based. Oracle Analytics does not use transformation tables to create metrics; however, some Retail Insights views in Oracle Analytics are created based on transformation tables. Those views are used to create some complex metrics such as Comp, Comp Base, and BOH (beginning on hand).
Time conversions are used to compare values from different time periods to discover and analyze time-based trends. Some examples of common time conversions are:
-
This year versus last year
-
Month-to-date comparisons
Any time conversion function can be included as part of the definition of a metric. For example, applying the Last Year conversion to a Sales Value metric creates a Sales Value (Last Year) metric that calculates the sales for last year. Multiple conversions can be applied to the same metric.
Each time conversion in Retail Insights is defined at all the levels applicable for that transformation. For example:
-
The Last Week conversion is defined at the day and week levels.
-
Last Month is defined at the day, week, and month levels.
-
Last Year is defined at the day, week, month, quarter, half-year, and year levels.
These definitions improve query performance.
The Last Year time transformation works differently depending on whether the retailer is using the Gregorian calendar or Business calendar. For the Gregorian calendar the Last Year corresponds to the current week last year, while for the Business calendar the Last Year will be 52 or 53 weeks from the current week.
Example Time Conversions
The following are some examples of the time conversions in Retail Insights.
Table A-1 Time Conversions
| Conversion | Summary |
|---|---|
| Last Month | Returns the corresponding last month fact data for the time period selected. |
| Last Week | Returns the corresponding last week fact data for the time period selected. |
July 14, 2026 Appendix A-1 of A-2
Appendix A
Table A-1 (Cont.) Time Conversions
| Conversion | Summary |
|---|---|
| Last Year | Returns the corresponding last year fact data for the time period selected. |
| Gregorian Last Year | Returns the corresponding last year fact data for the unshifted Gregorian calendar (e.g. from January 1st). |
| Month to Date | Returns the corresponding month-to-date fact data for the time period selected. |
Examples of Metrics That Use Time Conversion
The following are some metrics that illustrate time conversion capabilities. You can also extend these metrics for other time transformations. For more information, see the Oracle Fusion Middleware Metadata Repository Builder’s Guide for Oracle Analytics Server .
Table A-2 Metrics that use Time Conversion
| Metric | Summary |
|---|---|
| Net Sales Amt | Sales amount excluding returns. It indicates the actual money amount received from sales. |
| Net Sales Amt LY | Last year’s difference of gross sales amount minus returns amount. |
| Net Sales Amt LY YTD | Last year’s year-to-date difference between gross sales amount and returns amount. |
| Net Sales Amt GLY YTD | Last year’s Gregorian year-to-date difference between gross sales amount and returns amount. |
| Net Sales Amt MTD | Month-to-date difference between gross sales amount and returns amount. |
| Net Sales Amt LW | Last week’s difference between gross sales amount and returns amount. |
| Net Sales Amt MTD Var LY | Month-to-date net sales amount variance compared to last year. This metric tests a retailer’s sales performance on business-critical months such as holidays or the end of the financial year. |
| Net Sales Amt WTD | Week-to-date difference between gross sales amount and returns amount. |
| Net Sales Amt YTD | Year-to-date difference between gross sales amount and returns amount. |
July 14, 2026 Appendix A-2 of A-2
In this guide
- Guide: Retail Insights Cloud Service User Guide
- Previous: 6 Metrics
- Next: B Retail Insights Metric Definitions