Calculations in crosstabs

If we create at a crosstab based on Profit per Customer Country (attribute) and by Time Year (from the Sales cube), we should end up with a crosstab with 14 rows and 4 columns (not counting the Total row/column) like this:

mceclip0.png

This crosstab, with the reference synatx in mind, can be broken down to:

  • Columns: Years, d1 to d4 – or d-1 to d-4 (if counted from right to left).
  • Rows: Countries, d1 to d14 – or d-1 to d-14 (if counted from bottom to top).
  • Measures: Profit, m1. This crosstab contains only one measure.

To calculate the difference between e.g. the last two years, we can do this with a Custom calculation:

mceclip1.png

We could also add an extra calculated column, Growth last two years, like this:

mceclip2.png

With Growth number formatted as Percent we should end up with a result like this:

mceclip3.png

Furthermore, we could add an average calculation for each year like this:

mceclip4.png

With the average calculation included, we should get a result like this:

mceclip5.png

Notice the two blank cells in the intersection between the calculated columns and the calculated row. They are blank simply because TARGIT does not know if you prefer to calculate an average for the diff and growth valuesor if you prefer to calculate a diff and a growth for the average values.

Supposing we decide to calculate the diff and the growth for the average values, we should re-open the the Diff and the Growth calculation to make sure that the Average is included in the Calculate in intersection setting:

mceclip6.png

In this way, we will eventually get to a result like this:

mceclip7.png

Was this article helpful?
0 out of 0 found this helpful

Comments

0 comments

Please sign in to leave a comment.