TCONVERT

The TCONVERT function converts time-series data from one dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR to another dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR. You can specify an aggregation method or an allocation method to use in the conversion.

Return Value

The value returned by the TCONVERT function depends on the type of conversion you specify and the type of the dimension being converted.

Syntax

TCONVERT(expression time-dimension method [method])

where the syntax for method varies by method type:

Arguments

expression

An expression whose values you want to convert. Expression must be dimensioned by a dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR. This dimension is referred to as the source dimension. Usually expression is numeric, but with some conversion methods you can also convert text data. See "Converting Text Data".

time-dimension

The DAY, WEEK, MONTH, QUARTER, or YEAR dimension to which you want to convert the expression. This dimension is referred to as the target dimension.

method

The method to use for converting data from the source dimension to the target dimension. You can specify an aggregation method or an allocation method:

• Aggregation methods are SUM, AVERAGE, and LAST. They are typically used to convert data from smaller time periods to larger time periods (for example, months to years).

• Allocation methods are SPLIT, REPEAT, and INTERPOLATE. They are typically used to convert data from larger to smaller time periods (for example, years to quarters). The allocation methods all use the full default status of the source dimension to determine the periods that contribute to the allocation.

Except for a case in which the source dimension and target dimension have overlapping periods of equal length (as with a calendar year and a fiscal year), you can specify both an aggregation method and an allocation method. See "Compatible Aggregation and Allocation Methods" and "Using Both Aggregation and Allocation".

For all methods, results are calculated for the values in the current status of the target dimension.

The results you obtain depend on the method you specify and on whether you convert data between dimensions with periods of equal length or unequal length. See "Using Both Aggregation and Allocation", "Overlapping Periods of Equal Length", and "Substituting a Compatible Method".

SUM [BY PERIOD]

Aggregates data to a target period by totaling the data of the contributing source periods. For each target period, SUM BY PERIOD returns the total for all the source periods that end in the target period. SUM uses the implicit relation between the source and target dimensions.

SUM BY DAY

Weights each source value according to the portion of target days it represents. For each target period, SUM BY DAY multiplies each contributing source period value by a weighting factor that has this form where source-days-in-target is the Number of source-period days that actually fall in target period and total-days-in-period is the total number of days in source period:

source-days-in-target / total-days-in-period

SUM BY DAY then returns the total of these weighted source values. When you use SUM BY DAY, the value of an individual source period may be apportioned across adjacent target periods.

For example, suppose you convert weekly data to monthly data. When three days of a week fall in January and four fall in February, then SUM BY DAY adds 3/7 of the data for that week to the January total and 4/7 to the February total. In contrast, SUM BY PERIOD adds the entire data value for the week to the February total (since the week ends in February).

As another example, suppose you want to convert calendar year data to a fiscal year ending in June. Calendar year 1996 (Cal96) is the only calendar year that ends in fiscal year 1997 (Fy97). The SUM BY PERIOD method assigns the value for Cal96 to Fy97. In contrast, SUM BY DAY apportions the Cal96 value to the fiscal years Fy96 and Fy97, according to the number of calendar days that fall in each fiscal year. Of the 366 days of Cal96, 182 days (January 1 - June 30) fall in Fy96 and 184 days (July 1 - December 31) fall in Fy97. Therefore, for the CAL96 data, SUM BY DAY uses a weighting factor of 182/366 for Fy96 and a factor of 184/366 for Fy97.

AVERAGE [BY PERIOD]

Aggregates data to a target period by averaging the data of the contributing source periods. For each target period, AVERAGE BY PERIOD adds up the data from all the source periods that end within the target period and divides this total by the number of source periods. AVERAGE BY PERIOD uses the implicit relation between the two time dimensions.

AVERAGE BY DAY

Weights the value of each contributing source period by the portion of target days it represents. For each target period, AVERAGE BY DAY multiplies the value of each source period by the number of days of that source period that actually fall within the target period. The average is then calculated by adding these weighted source values and dividing by the total number of days in the target period. When you use AVERAGE BY DAY, the value of a single source period may be apportioned across adjacent target periods.

LAST [BY PERIOD]

For each target period, LAST BY PERIOD returns the data value from the last source period that ends within the target period. It uses the implicit relation between the source and target dimensions.

LAST BY DAY

Has the same effect as LAST BY PERIOD, provided you are converting data from smaller periods to larger periods. See "Substituting a Compatible Method".

STATUS

Indicates that the current status of the source dimension is used. It is the default for the SUM and AVERAGE methods.

NOSTATUS

Indicates that the full default status of the source dimension is used. It is the default for the LAST method.

SPLIT [BY PERIOD]

Allocates data to target periods by splitting the data from the source periods. SPLIT BY PERIOD divides a source value evenly among the target periods that end in that source period. SPLIT BY PERIOD uses the implicit relation between the two DAY, WEEK, MONTH, QUARTER, or YEAR dimensions.

SPLIT BY DAY

Weights each source value according to the portion of target days it represents. For each target period, SPLIT BY DAY multiplies each contributing source period value by a weighting factor that has this form where target-days-in-source is the Number of target-period days that actually fall in source period and total-period-days is the total number of days in source period:

target-days-in-source / total-period-days

SPLIT BY DAY then returns the total of these weighted source values. When you use SPLIT BY DAY, the value of an individual source period may be apportioned across adjacent target periods.

REPEAT

For each target period, REPEAT returns the value of a source period. The target periods are the periods that end within the source period. REPEAT uses the implicit relation between the source and target dimensions. REPEAT BY DAY has the same effect as REPEAT BY PERIOD, provided you are converting data from larger time periods to smaller time periods. See "Substituting a Compatible Method".

INTERPOLATE [BY PERIOD]

The INTERPOLATE method allocates data to target periods by first calculating the difference between the values of the current and previous source periods, and then splitting the result incrementally over the target periods. INTERPOLATE divides the difference between the current and previous source period values by the number of target periods that end in the source period, and it increments each target period by this amount.

INTERPOLATE BY DAY

For each target period, adds the value of the previous source period to a value that is calculated as follows where end-days is the number of days from end of previous source period to end of current target period and period-days is the total number of days in current source period:

(end-days / period-days) * (current-source-value - previous-source-value)

When a target period has days that fall in more than one source period, a similar calculation is made for each source period.

Notes

Dimensions of the Result Returned by TCONVERT

The results returned by TCONVERT are dimensioned by the target DAY, WEEK, MONTH, QUARTER, or YEAR dimension and by all of expression dimensions that are not DAY, WEEK, MONTH, QUARTER, or YEAR dimensions.

Status Used with Allocation

The STATUS and NOSTATUS keywords have no effect with the allocation methods. The allocation methods always use the full default status of the source dimension to determine the contributing periods.

Compatible Aggregation and Allocation Methods

Except for a case in which the source dimension and the target dimension have overlapping periods of equal length, you can specify both an aggregation method and an allocation method. However, the two methods must be compatible. Table 23-1, "Compatible Aggregation and Allocation Methods" shows the compatible methods.

When you specify both an aggregation method and an allocation method, you can specify BY PERIOD or BY DAY with either method. When you specify BY PERIOD (explicitly or by default) for one method and BY DAY for the other method, BY DAY takes precedence.

Using Both Aggregation and Allocation

When you specify both an aggregation method and a compatible allocation method, Oracle OLAP handles this as follows:

• When you convert data from smaller periods to larger periods, Oracle OLAP uses the aggregation method (with BY DAY, if specified for either method).

• When you convert data from larger periods to smaller periods, Oracle OLAP uses the allocation method (with BY DAY, if specified for either method).

• When you convert data between dimensions that have non-overlapping periods of equal length, such as a quarter ending in March and a quarter ending in June, the results of the aggregation and allocation methods will be identical.

Overlapping Periods of Equal Length

When you convert data between two dimensions of type DAY, WEEK, MONTH, QUARTER, or YEAR that have overlapping periods of equal length, such as a calendar year and a fiscal year, or a quarter ending in March and a quarter ending in April, you must specify either an aggregation method or allocation method, but not both. For these dimensions, the compatible aggregation and allocation methods may yield different results.

For example, when you convert data from a calendar year dimension to a fiscal year dimension that ends in June, the SUM and SPLIT methods will return different results:

• The SUM method totals up the data from the source periods that end in the target period. Since the calendar year 1996 ends in fiscal year 1997, the SUM method assigns the value for calendar year 1996 to fiscal year 1997.

• The SPLIT method allocates a source data value to the target periods that end in the source period. Since the fiscal year 1996 ends in calendar year 1996, the SPLIT method assigns the value for calendar year 1996 to fiscal year 1996.

Substituting a Compatible Method

When you specify a single conversion method, and you use an aggregation method to convert data from a larger period to a smaller period (for example, from months to weeks) Oracle OLAP automatically uses the compatible allocation method in place of the specified aggregation method. Similarly, when you use an allocation method to convert data from a smaller period to a larger period, Oracle OLAP automatically uses the compatible aggregation method. See "Compatible Aggregation and Allocation Methods".

Data Type of the Result

When possible, TCONVERT returns results that have the same data type as expression. When expression is DECIMAL, the results are always DECIMAL. When expression is INTEGER, the results are INTEGER when the required calculations do not involve division. For example, when two dimensions are aligned (that is, they have the same phase and are based on the same periods, such as a calendar year dimension and a quarter dimension ending in December), the result is INTEGER when you use the REPEAT method to convert an INTEGER expression from larger periods to smaller periods. Similarly, the result is INTEGER when you use the SUM or LAST method to convert the expression from smaller to larger periods.

Converting Text Data

You can also use TCONVERT to convert the values of a text expression when no numeric calculations are needed for the conversion. For aligned dimensions, for example, you can use the LAST method to convert text values from smaller periods to larger periods, and you can use the REPEAT method to convert text values from larger periods to smaller periods. You can also use the LAST and REPEAT methods to convert text data between dimensions that have periods of equal length. When you attempt to convert a text expression with a method that requires numeric calculations, you will receive an error message.

Methods for Financial Data

When you work with financial data, you can use an appropriate conversion method for each type of data. Table 23-2, "Examples of Conversion Methods for Different Types of Financial Data" gives some examples.

How TCONVERT Handles NA Values

TCONVERT is affected by the NASKIP option. When NASKIP is set to NO, TCONVERT returns an NA value for any target period that receives contributions from a source period with an NA value.

Examples

DEFINE budget.revenue DECIMAL <year>
budget.revenue(year 'Yr96') = 120000
LIMIT quarter TO year 'Yr96'
REPORT W 12 HEADING 'Split Evenly' -
   TCONVERT(budget.revenue quarter SPLIT) -
   W 12 HEADING 'Split by Day' -
   TCONVERT(budget.revenue quarter Split by day)

QUARTER        Split Evenly Split by Day
-------------- ------------ ------------
Q1.96             30,000.00    29,836.07
Q2.96             30,000.00    29,836.07
Q3.96             30,000.00    30,163.93
Q4.96             30,000.00    30,163.93

DEFINE week DIMENSION WEEK
MAINTAIN week ADD '01Jan96' '30Jun96'

DEFINE weekvar DECIMAL <week>
weekvar = 7

SHOW BEGINDATE(weekvar)
SHOW ENDDATE(weekvar)

31Dec95
06Jul96

LIMIT month TO 'Jan96' TO 'Jul96'
REPORT HEADING 'Sum' TCONVERT(weekvar month SUM) -
   HEADING 'Sum by Day' -
   TCONVERT(weekvar month SUM BY day)

MONTH              Sum    Sum by Day
-------------- ---------- ----------
Jan96               28.00      31.00
Feb96               28.00      29.00
Mar96               35.00      31.00
Apr96               28.00      30.00
May96               28.00      31.00
Jun96               35.00      30.00
Jul96                7.00       6.00