Sometimes the data sequence (sorting) can be important in a calculation.
Maybe even to the point where your calculation will break if someone (an end-user) sorts the data differently than the original sorting sequence.
Here is an example where this is the case.
In this table, we are calculating a January index which works when January is the first row.
The syntax behind this index looks like this:
sum(d1, 0, m1) / sum(d1, d1, m1) * 100
The calculation looks at the current row (0) and divides that by the first row (d1), and multiplies by 100.
If the data is sorted by the end user, eg, in descending order based on the revenue, it will actually break the logic that supports the calculation:
The January index has now turned into an August index.
To prevent this from happening, we can just add the (u) modifier to our original syntax.
sum(d1, 0, m1) / sum(d1, d1(u), m1) * 100
Now the sorted version of the crosstab will still calculate according to the original (unsorted) sequence of data:
In conclusion, everywhere you refer to certain rows in any calculation, you should consider adding the (u) modifier.
Please sign in to leave a comment.