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 BI does not use transformation tables to create metrics; however, some Retail Insights views in Oracle BI 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.
Last Year	Returns the corresponding last year fact data for the time period selected.
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 Business Intelligence Enterprise Edition.

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