Cross-Row and Column References for Crosstabs

From the latest blog post you could already tell that the crosstab is one of our focus areas for version 22. Besides multiple result cells we decided to pay a visit to the crosstab functions as well. In version 21 these functions can already be used to reference the values of sum cells, column and row labels etc. By extending the functions slightly, striking new features become available.

The most important use case for these new functions is cross-referencing of results. Consider the revenue statistic from the last blog post:

New feature of version 22

Now in addition to the total revenue, we’d like to display the percentage each category contributes to the total. The dialog for adding result cells has a new tab “Display as” that just serves this purpose:
 

New feature of version 22

The resulting crosstab needs some more tweaking, here is the raw result:
 

New feature of version 22

We’d probably like to add a percentage formatter to the new cells. Besides, we don’t really need the fraction on the “Quarter” level, we’d just like to have it on the “Total” level. And the row header for the fraction should read “Fraction”, not “Revenue”.
These are easy changes. In order to only show the fraction as total cell, simply set the appearance condition of the “normal” cell to “False”. The percentage formatter is available as usual via the “Format” property, just as the row header can be set via the “Row Header” property. The final result in all its glory looks like this:

New feature of version 22

This is only one use case for the new functionality. In our sample, it also enables comparisons per quarter (how well did this quarter do compared to the last quarter / the same quarter last year) or year. These comparisons can be absolute (revenue change) or relative (revenue change percentage). Again, this is something that was close to impossible to do before unless you are a GetVar/SetVar Black-Belt-Ninja.

Related Posts

Leave a Comment