Many people are aware of the use of OVER in a custom expression, however one issue that comes up in almost every training is when a user has created an expression using OVER on either the x or y axis the results are not what they expected. Typically this is because the plot has a defined slicing in another axis as well, like the color, trellis, shape or size access and the OVER method has not properly taken that into account.
Assume we have the following data table:
If we display a Bar Chart with the Name of on the X-Axis, and the Sum of the Number on the Y-Axis, the plot would look like the one below:
This will slice the data table by 'Name'. We can also add a Color-axis (as well as any other axis) as shown below:
NOTE: Each time you make a choice in a selector dropdown, an intersection take place in your data table. (Each individual slice, is an intersection of all slicing properties in that particular visualization.) In the chart above, slicing is based off 'Type' and 'Name' and you can think of the data as being sliced as shown below:
If we want to then create an expression that gives us the sum of the each individual item OVER the sum of all items, many try to use the following expression:
Sum([Number]) / Sum([Number]) OVER (All([Axis.X]))
But when we do this, the numbers are off, as you can see in the plot below.
If you add up the totals for each Bar, it adds up to 2 not 1. Why? This is because your expression did not take into account the plot is slicing the data by not just the x-axis ('Name' in this case), but also by Color ('Type' in this case).
Since the Color axis is not taken into account in the expression, the calculation is grouping the sums by Type: 'fruit' and 'vegetable'. This is why the 'Apple' and 'Banana' Bars add up to 1 (since they are all the fruit types in the data table), and the 'Broccoli' Bar adds up to 1 (since it is the only fruit type in the data table).
To ignore the Color-axis slicing as well, we must use the Intersect method.
Sum([Number]) / Sum([Number]) OVER (Intersect(All([Axis.X]),All([Axis.Color])))
The result of this expression is shown below:
The reason this works is because the OVER (Intersect(All([Axis.X]),All([Axis.Color]))) part of the expression tells us to ignore both the X-axis slicing and Color slicing.
NOTE: If you added another slicing axis into the plot, like size or shape or trellis, you would then also need to add that related portion into the Intersect method if you wished to ignore it when performing the custom expression calculation.
Remember these simple steps related to slicing and the user of OVER and the Intersect method in Custom Expressions:
- An intersection will occur each time an axis value is defined (x, y, color, trellis, shape, size, etc...) , and will slice your data inside the plot
- Each individual unit/item displayed in the plot is an intersection of all slicings defined in that particular plot
- The OVER method when used in Custom Expressions is really telling the plot to ignore specific slices that are already built into the plot
Interested in learning more about Custom Expressions and how to use them? Please take SP141 Computational Analytics.
