Time transformations are used in metrics to compare values across different time periods, such as the current year versus the previous year, or the current date versus year-to-date/quarter-to-date/month-to-date totals. It is important to note that when comparing the current year to the previous year, data can exist at different levels: yearly (2023 vs. 2022), quarterly (Q1 2023 vs. Q1 2022), monthly (Jan 2023 vs. Jan 2022), or daily (01.01.2023 vs. 01.01.2022). These comparisons are described by different attributes.
For the previous year transformation, a key component is a table that maps the ID of a given year to the ID of its corresponding previous year. Such tables are commonly referred to in databases as LU (lookup) tables.
The above-mentioned table, specifically the column ROK_ID (YEAR_ID), must be mapped to the Year attribute.
The next step is to create a new transformation in:
Project\SchemaObjects\Transformations.
To create a report that displays the value of a selected metric for the current and the previous year, you need to define a new metric. In its formula, include the transformation that was created earlier.
An example report will look like this:
In this transformation, the relevant components will look as follows:
1) Lookup table
2) Transformation:
3) Report:
