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.
Important:
You can only use this function with dimensions of type DAY, WEEK, MONTH, QUARTER, or YEAR.You cannot use this function for time dimensions that are implemented as hierarchical dimensions of type TEXT.The value returned by the TCONVERT function depends on the type of conversion you specify and the type of the dimension being converted.
TCONVERT(expression time-dimension method [method])
where the syntax for method varies by method type:
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".
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.
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".
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.
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 (because 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.
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.
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.
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.
Has the same effect as LAST BY PERIOD, provided you are converting data from smaller periods to larger periods. See "Substituting a Compatible Method".
Indicates that the current status of the source dimension is used. It is the default for the SUM and AVERAGE methods.
Indicates that the full default status of the source dimension is used. It is the default for the LAST method.
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.
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.
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".
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.
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 multiple source periods, a similar calculation is made for each source period.
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.
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 8-15, "Compatible Aggregation and Allocation Methods" shows the compatible methods.
Table 8-15 Compatible Aggregation and Allocation Methods
| Aggregation | Compatible Allocation |
|---|---|
|
SUM |
SPLIT |
|
AVERAGE |
REPEAT |
|
LAST |
INTERPOLATE |
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 are 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 return different results:
The SUM method totals up the data from the source periods that end in the target period. Because 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. Because 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 instead 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".
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.
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 receive an error message.
When you work with financial data, you can use an appropriate conversion method for each type of data. Table 8-16, "Examples of Conversion Methods for Different Types of Financial Data" gives some examples.
Table 8-16 Examples of Conversion Methods for Different Types of Financial Data
| Type of Financial Data | Conversion | Conversion Method |
|---|---|---|
|
Revenue or expenses |
Month to year |
SUM |
|
Stock quotations |
Day to quarter |
AVERAGE |
|
Balance sheet items |
Month to quarter |
LAST |
|
Quarterly tax payment |
Year to quarter |
SPLIT BY PERIOD |
|
Money supply |
Year to quarter |
INTERPOLATE |
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.
Example 8-133 Splitting Data Across Quarters
This example shows the effects of using the SPLIT method and the SPLIT BY DAY method to allocate an annual budget revenue figure of $120,000 across the quarters of the year 1996. An existing year dimension is the source dimension and an existing quarter dimension is the target dimension.
The following statements
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)
produce this report.
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
Example 8-134 Aggregating Weekly Data to Monthly Using TCONVERT
This example aggregates weekly data to monthly data. First, define a week dimension named week and add weeks that include the dates January 1, 1996 and June 30, 1996 (Oracle OLAP automatically adds the intervening weeks).
DEFINE week DIMENSION WEEK MAINTAIN week ADD '01Jan96' '30Jun96'
Next, define a variable named weekvar, dimensioned by week, and assign a value of 7 to each week.
DEFINE weekvar DECIMAL <week> weekvar = 7
The following statements show that December 31, 1995 is the beginning date of the first week for which weekvar contains non-NA data and that July 6, 1996 is the ending date of the final week for which weekvar contains non-NA data.
SHOW BEGINDATE(weekvar) SHOW ENDDATE(weekvar)
The statements produce this output.
31Dec95 06Jul96
With these beginning and ending dates, when the data is converted to monthly data, it is aggregated over the months Dec95 through Jul96. The following statements show the effects of using the SUM method and the SUM BY DAY method to convert the weekly weekvar data to monthly data.
LIMIT month TO 'Jan96' TO 'Jul96' REPORT HEADING 'Sum' TCONVERT(weekvar month SUM) - HEADING 'Sum by Day' - TCONVERT(weekvar month SUM BY day)
These statements produce the following report.
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