24.2 Transformations¶
A transformation shifts or accumulates the dates a metric reads, e.g. same date last year, or from the beginning up to the selected date for inventory levels. Transformations are defined in Modeller and applied to metrics in Datashop – see 10.7 Classic Transformations.
For most period comparisons, Datashop's quick transformations are easier and more flexible – see 10.4 Quick Transformations. Use Modeller transformations for special date logic, such as fiscal calendars or balances.
Creating a Transformation¶
HOME → New → New Transformation (Ctrl+T). It appears under Transformations in the Object Tree as New transformation. Set it up under Properties:
- Dimensional unit – the date unit it works on, e.g. Date posted;
- Type – Relational, Expression or Accumulation.
Expression¶
An Expression transformation computes another date from the selected one. [x] stands for the selected value.
| Example | Expression |
|---|---|
| Same date last year (SQL Server date) | DATEADD(yy, -1, [x]) |
| Same date last year (date stored as yyyymmdd number) | [x] - 10000 |
| Same weekday last year | [x] -364 |
Inverse expression is the reverse formula, e.g. DATEADD(yy, 1, [x]). It lets Acinta transform the data instead of the filter, which works better when grouping by date. For the examples above Modeller finds the inverse automatically.
Accumulation¶
An Accumulation sums everything up to (or from) the selected period – for balances and inventory levels, where each period's value is the sum of all transactions before it.
- Accumulation direction: Backward (default for new transformations – from the beginning up to the selected period) or Forward.
- Value transformation: an optional expression applied as well, default
[x]. - Specify end value: limit the accumulation to an end value.
The date unit must be mapped in the fact table, or Datashop reports that the accumulation isn't applicable.
Relational¶
A Relational transformation uses a Conversion table to translate dates, e.g. a table mapping each date to the comparable date in a fiscal or retail calendar. Choose the table and its From field and To field.
Common Issues – Transformations¶
Problem: The '[x]' placeholder in transformation '…' is missing.
- Solution: Expressions must contain [x].
Problem: The transformation '…' has no dimensional unit assigned. - Solution: Choose the Dimensional unit.