Creating transformation metrics

Transformations allow you to apply an attribute-element offset for fact-comparison purposes. Although transformations can be applied to any attribute hierarchy, the one on which offsets are used most often is Time, in which case 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 provided with MicroStrategy. These functions can be used to define metrics that compare values from different time periods without the use of transformation metrics. For information on using these functions to support transformation-style analysis, see the Functions Reference.

Time-based transformations

Metrics use time transformations to compare values at different times, such as this year versus last year or current date versus month-to-date. The last year transformation maps each time period to its corresponding time period last year, while the month-to-date transformation maps each time period to a set of time periods that comprise the entire month to date.

The definition of the new transformation metric is:

Sum([Account Amount]) {~+} | [Last Quarter] |

In the metric definition, a transformation is placed between pipe symbols (|). (See the table of symbols in Metric Editor Formula tab: Metric level property symbols.) The two metrics are placed on a report with the Account Type attribute, as well as other metrics. The results are shown in the following figure.

Transformations are useful for such time-series analyses, which are relevant to many industries, including retail, banking, and telecommunications. 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 revenue versus this year's revenue, 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 revenue, add a filter for this year to the Revenue metric. Similarly, to calculate last year's revenue, use the Revenue 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 Revenue. With a single filter, on 2023 for example, the two metrics Revenue and Last Year Revenue give you results for 2023 and 2022, 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 time attribute contained on the report.

For an example of a year-to-date transformation, see the Advanced Metrics chapter of the Advanced Reporting Help.

Non-time-based transformations

While transformations are most often used for discovering and analyzing time-based trends in your data, not all transformations have to be time-based. For example, a transformation can map defunct product codes to new ones. An example of a non-time-based transformation is This Catalog/Last Catalog, which might use catalog_ID-1 to perform the transformation.

Prerequisites

• You apply a transformation to a metric when you create a transformation metric. If the transformation you need does not exist, you must create it before adding it to the metric. See Creating transformations in the Project Design Help for more information.

• You can apply transformations to simple metrics but not for compound metrics. The formula of a simple metric is based on group functions (such as sum or average). For more information, see Simple vs. compound metrics.

• This procedure assumes you are already working with a metric in the Metric Editor. If you are not, do one of the following:

  • Edit an existing metric

  • Create a simple metric

• Rather than using the Metric Editor to apply a transformation to a single metric, you can use the Advanced Metric Assistant to combine multiple, simple metrics with multiple transformations. This can help reduce the time required to create the transformation metrics for a project, as described in Creating metrics by combining metrics, filters, and transformations.

To create a transformation metric

1. Click Transformation on the Metric component pane (located under the heading "Metric (metric name) is defined as"). The Transformations definition pane displays below the Metric component pane.

2. To specify a transformation, double-click it in the Object Browser to add it to the Transformations definition pane.

3. To remove a transformation, select it in the Transformations definition pane and click Remove.

4. To remove all the transformations, click Reset.

5. To change the order of the transformations, select a transformation and use the directional arrows to reposition it.

6. Choose one of the following:

   • Save the metric

   • Continue creating the metric (for example, adding levels or conditionality)

Related Topic

• Creating simple metrics