Transformation metrics: Time-based and other comparisons

Transformations allow you to apply an attribute-element offset for fact-comparison purposes. Although transformations can be applied to any attribute hierarchy, the Calendar hierarchy is used most often. For the Calendar hierarchy, the difference can be set as a fixed number of days, weeks, months or years.

Transformation-style analysis can also be supported using the Lag and Lead functions. These functions can be used to define metrics that compare values from different time periods without the use of transformation metrics.

Notes:

XBRi provides prebuilt transformations. Accordingly, this topic focuses on how to use transformations in metrics and reports, and provides only an overview of transformations in general.
You can also create transformation shortcut metrics, which are a quick way to add new metrics based on the existing metrics of a report.

Date-based transformations

Metrics use date transformations to compare values at different times, such as this year versus last year or this month versus last month. The last year transformation maps each date period to its corresponding date period last year, while the prior period transformation maps each date period to the date period immediately preceding it.

The definition of the new transformation metric is:

Sum([Refund Amount]) {~+} | [Calendar - Last Year] |

In the metric definition, a transformation is placed between pipe symbols (|). The two metrics are placed on a report with the Store attribute, as well as other metrics.

Transformations are useful for such time-series analyses. Another typical example of this type of analysis is a TY/LY comparison (This Year versus Last Year). To calculate a variance or a growth percentage such as last year’s net sales versus this year’s net sales, a transformation is convenient and flexible, although there are alternatives.

For example, you can use filters to create the TY/LY comparison. To calculate this year’s net sales, add a filter for this year to the Net Sales metric. Similarly, to calculate last year's net sales, use the Net Sales metric in conjunction with a filter for last year. However, a more flexible alternative is to use a previously created Last Year transformation in the definition of a new metric, called Last Year Net Sales. With a single filter, on 2013 for example, the two metrics Net Sales and Last Year Net Sales give you results for 2013 and 2012, respectively. In this example, two filters were created for the report, while the transformation needs only one. The same transformation metric can be applied to a report with a different filter to achieve different results, while, without a transformation, new filters would have to be created to build a different report. Transformations are usually the most generic approach and can be re-used and applied to other time-series analyses.

Since a transformation represents a rule, it can describe the effect of that rule for different levels. For instance, the Last Year transformation intuitively describes how a specific year relates to the year before. It can also express how each month of a year corresponds to a month of the prior year. In the same way, the transformation can describe how each day of a year maps to a day of the year before. This information defines the transformation and abstracts all cases into a generic concept. That is, you can use a single metric with a last year transformation regardless of the date attribute contained on the report.

Prerequisite

The following procedure creates a transformation metric by using the Condition metric template, which allows you to quickly and easily begin creating the metric. The Transformation template uses the Sum function and opens the Transformation area so that you can quickly select the transformation to apply to the metric. You only have to select which fact to sum, as well as the transformation, to create the metric.

You can instead create the metric by defining the metric's formula (the function and expression) yourself. The metric must use a grouping function (such as Sum or Average) to have a transformation added to it. For steps to create a metric that uses a grouping function, see Creating a metric that uses a grouping function. If you have already defined the metric formula, begin this procedure at Add a transformation.

To add a transformation to a metric:

Navigate to the Shared Reports or My Reports folder or to the Search page.
From the icon bar, click the Create Metric icon. This displays the Select a Function or Template dialog box.
From the Select a category drop-down list, select Metric Templates.
Select Condition, and click OK. This displays the Function Editor.
The function is automatically set to Sum, but you can select a different function from the Aggregation Function drop-down list.

The expression of a metric is what the function is applied to. The expression can be a fact, an attribute, or another metric. Specify the expression by doing one of the following:

To specify the expression by typing the name of a object, type the name of the object in the Expression field. As you type, matching objects are displayed in a drop-down list. You can click an object or continue to type. You can type multiple objects, such as Net Sales-Refund Amount.
To specify the expression by choosing an object, click the Browse icon . The Select an Object dialog box opens. Either navigate to and select an object, or search for an object.

Add a transformation

Note: If the Transformation area is not already open, expand the Transformation option.

Select a transformation to place on the metric, by clicking the Browse icon next to the Transformation field. This displays the Select an Object dialog box. Either navigate to and select a transformation, or search for a transformation. After you select a transformation, you are returned to the Function Editor.

You can add more transformations to the metric, if needed. Repeat the step above.
To remove a transformation, click the Delete icon next to the transformation.
To change the order of the transformations, select a transformation and use the directional arrows to move it up or down.

Complete the metric

When you have finished adding transformations to the metric, you can add levels or a condition, or save the metric.

- For steps to add levels, see To add a level to a metric. A level allows you to specify the attributes to use in the metric calculation, regardless of what is contained on any report the metric is placed on. By default, a metric is calculated at the level of the attributes on the report. For a more detailed description of levels, including examples, see Level metrics.
- For steps to add a condition, see To add a condition to a metric. A condition allows you to apply a filter to only one metric on a report while not affecting the other metrics. For a more detailed description of conditional metrics, including examples, see Conditional metrics.
- To save the metric, click Save to save your changes. The Save As dialog box opens. Navigate to the folder where you want to save the metric. Type a Name and Description for the metric, and click OK. Your new metric is saved, and you are returned to the Function Editor.