Transformation Metrics: Time-Based and Other Comparisons

You need to compare business unit costs on a quarterly basis. For example, the business unit costs are represented by the Account Amount fact, which is the actual amount of each recorded transaction. To calculate the Account Amount, create the following metric, called Actual Amount, to calculate the dollar value of the accounts:

Sum([Account Amount]) {~+}

To count last quarter's costs, you apply a transformation to your Account Amount metric. A transformation maps a specified time period to another time period. In other words, it applies an offset value, such as current quarter minus one quarter.

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 objects. For information on using these functions to support transformation-style analysis, see the Functions Reference.

Time-Based Transformations

Time transformations are used in metrics 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.

MicroStrategy provides numerous prebuilt transformations, although you can create your own as needed. One of the MicroStrategy-provided transformations answers our needs for the following example.

Transformations are schema objects and therefore only a project designer with the Create schema objects privilege can create them. Accordingly, this section focuses on how to use transformations in metrics and reports, and provides only an overview of transformations in general. For information about transformations and how to create them, see the Transformations chapter of the Project Design Help.

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 Level Symbols: Defining Expression Syntax.) 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. Therefore, you can use a single metric with a last year transformation regardless of the time attribute contained on the report.

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.