Calculated Crosstab Columns

Overview

This topic provides you with information on the use of Calculated Crosstab Columns when building or updating a Crosstabulation Definition.

Sum column X and column Y

Adds values for column X and column Y; e.g. 0102  adds the values in columns 1 and 2, this can also be done on any two non-consecutive columns e.g. column 1 and Column 4 (0104) would work the same way.

Range sum column X to column Y

This is used to add all the values in the range column X to column Y; e.g. 0104 adds the values in columns 1 to 4 inclusive. This is regularly used to create sub-totals from a large number of columns.

Variance column X vs column Y

Calculates the variance between the values in column X and column Y; e.g. 0102 shows the difference between the values in columns 1 and 2. This is important when viewing, for example, Planned vs. Actual data to see how the two compare and whether planned targets have been met. It is also useful when comparing two similar customers such as Wholesaler1 and Wholesaler2 as shown below.

Column X as % of column Y

Calculates the percentage ratio of the value in column X to that in column Y; e.g. 0102 shows the value in column 1 in terms of the value in column 2 as a percentage, therefore if Column X is smaller than Column Y, the result will be under 100%. This is good to use when looking at YTD values as a percentage of planned values for the whole year.

Column X divided by column Y

Calculates the ratio of the value in column X to that in column Y e.g. 0102 divides the value in column 1 by the value in column 2.

Column X multiplied by column Y

Calculates the product of the values in columns X and column Y; e.g. 0102 multiplies the values in columns 1 and 2. This is useful when you need to work out figures based on a ratio, as can be seen here.

% Variance column X vs. column Y

Calculates the percentage variance between the values in columns X and column Y; e.g. 0102 finds the % variance between the values in columns 1 and 2. This is useful to understand the percentage difference between two columns and will show you how far ahead or behind you are compared to the Column Y variable.

Column X multiplied by a factor

Multiplies the value in column X by a constant; e.g. 0215.6 multiplies the value in column 2 by the factor 15.6.  Note that decimal values must use the local Windows decimal separator. This is useful when forecasting ahead to provide targets in the future. The following will create a 20% increase on the figures in Column 1.

Column X divided by a factor

Divides the value in column X by a constant; e.g. 024 divides the value in column 2 by the factor 4.  Note that decimal values must use the local Windows decimal separator. This is useful for looking at averages across a year instance, where dividing by a factor of 4 will give you average values for each quarter.

% Total

Calculates the % contribution of the specified variable to the overall total. This can be used to identify the percentage of each product the customer sells within a specified range, here using just CNS products.

% Total for the line

Calculates the % contribution of the line to the overall total.

Absolute Variance Column X vs Column Y

Looks at the absolute exchange variances between the two columns.

For other Column Types, see Crosstabulation Column Types.