Hi, could we please add a transformation called Month-over-Month Percentage Change / MoM % Change?
Problem:
The dataset contains one row per month and category. Each row has the current value, but the previous month’s value is not directly available in the same row.
Example:
Month
Category
Value
1
A
100
2
A
120
3
A
90
1
B
50
2
B
75
To calculate the percentage change, the transformation first needs to find the value from the same category in the previous month.
For the first month, or if no matching previous-month value exists, the result should be NULL.
In abstract terms:
The transformation should calculate the relative percentage change between a current row and the matching previous-period row, grouped by the selected dimension/category.
you can do it without new transformation, it isn’t straightforward (maybe there are faster ways). The main trick is the Offset transformation to move values up or down in different rows.
Problem:
The dataset contains one row per month and category. Each row has the current value, but the previous month’s value is not directly available in the same row.
In addition, the month alone is not enough for matching. The data can contain multiple categories per month, for example:
Month
Category
Value
1
A
100
1
B
50
1
C
200
2
A
120
2
B
75
2
C
180
So the transformation must not simply compare month 2 with the previous row.
It has to compare:
2 / A with 1 / A
2 / B with 1 / B
2 / C with 1 / C
In abstract terms:
The transformation should find the matching previous-period row based on period + selected grouping dimension(s), then calculate the percentage change. This is important to avoid mixing categories within the same month.
maybe not as new transform maybe as function for example in calculate transform- i would not need the percentage - that is not my pain, my pain - is the right grouping for each period
Two sort statements should solve it the first one a sort by Category and Month as second criteria (both Ascending). This should be done before the other logic. Second sort after calculation logic: Sort be Month and here Category as second criteria.
I think it works correct, there is an issue with th exhuming values from negative to positive and vice versa. It will create percentages with changing signs and the jumps are large and show therefore percentages up in thousands.
And if you go from -10 to 20, you get a a percentage of -200%
