Advanced Custom Expressions

The OVER statement is used in many of the more advanced custom expressions. In this section, several node and time period methods are explained.

In the examples below, a bar chart with a Year/Month hierarchy on the category axis is used, and in most cases it is colored by Product.

For some of the methods, there is more than one example and many have a more general illustration of how they work.

Node methods

Node methods can refer to other nodes relative to the current node based on the position in the hierarchy.

Method

Description

Parent Method

Uses the parent subset of the current node to perform the calculation. If the node does not have a parent, all rows are used as the subset.

It can be used to show how much of the total sales each product category represent each month.

Sum([Sales]) / Sum([Sales]) OVER (Parent([Axis.Color]))

Note: In this example, Parent refers to what the color is set to, which is Product.

As an additional example, if you wish to see the percentage of all sales for each month of each year, regardless of product category, you can create the following custom expression.

Sum([Sales]) / Sum([Sales]) OVER (Parent([Axis.X])) * 100

Next Method

Compares the current node with the next node on the same level in the hierarchy.

If there is no next node, that is, if the current node is the last node for the current level, the resulting subset will not contain any rows.

Sum([Sales]) - Sum([Sales]) OVER (Next([Axis.X]))

If the current node is 2004.Jan, in the figure at the top of the page, 'next' in this case will refer to 2004.March, since 2004.February is missing in the data. See Next Period method for value based reference.

Previous Method

Uses the previous node on the same level as the current node to compare the result of the current node with the previous one.

If there is no previous node, that is, if the current node is the first node for the current level, the resulting subset will not contain any rows.

Sum([Sales]) - Sum([Sales]) OVER (Previous([Axis.X]))

All Method

The All method uses all the nodes in the referenced hierarchy:

This can be useful when intersecting the current node with more than one hierarchy.

In this example, the bar chart shows the relative sales of the different product categories for each month.

Sum([Sales]) / Sum([Sales]) OVER (Intersect(All([Axis.Color]), [Axis.X]))

Note: See the Intersect Method for information on that method.

Another example is if you want to see the percentage of all sales for each month over the years. Regardless of product category.

Sum([Sales]) / Sum([Sales]) OVER (All([Axis.X])) * 100

All Previous Method

Uses all nodes, including the current, from the start of the level.

This can be used to calculate the cumulative sum.

Sum([Sales]) OVER (AllPrevious([Axis.X]))

Tip: A basic Cumulative Sum expression can easily be created using the shortcut in the Aggregation menu on the axis selector.

If you want to limit the cumulative sum for current year you can combine the All Previous Method with the Intersect Method:

Sum([Sales]) OVER (Intersect(Parent([Axis.X]), AllPrevious([Axis.X])))

Note: See the Intersect Method for information on that method.

All Next Method

Uses all nodes, including the current, to the end of the level.

Intersect Method

Returns the intersected rows from nodes in different hierarchies.

See also the All Previous Method and the All Method for examples of how it works.
Intersect(Parent([Axis.X]), All([Axis.Color]), Parent([Axis.Rows]), ...)

Time period methods

Time period methods can refer to other nodes relative to the current node based on the value in the current node and the other relative nodes.

Method	Description
Next Period Method	Uses the next node which has the next value (defined as the next value index) on the same level as the current node to perform the calculation. If there is no next node, that is, if the current node is the last node for the current level, the resulting subset will not contain any rows. Sum([Sales]) OVER (NextPeriod([Axis.X])) If the current node is 2004.Jan, in the figure at the top of the page, NextPeriod will return an empty subset, since February is missing.
Previous Period Method	Uses the previous node which has the previous value (defined as the previous value index) on the same level as the current node to perform the calculation. If there is no previous node, that is, if the current node is the first node for the current level, the resulting subset will not contain any rows. Sum([Sales]) OVER (PreviousPeriod([Axis.X]))
Parallel Period Method	Uses the previous parallel node with the same value (defined as having the same value index) on the same level as the current node. Note: The method only looks for a node with the same value as the current node in the children to the previous parent node. That means that it only looks one level above the current node. Therefore, a hierarchy with year as the parent node and quarter OR month as nodes on the lower level will work since the nodes under each year will have the same values. However, a hierarchy with all three levels would not work for this method since the months under Q2 and Q3, for example, do not have the same values. This can be used to compare sales results for each month with the corresponding months the previous year. Sum([Sales])-Sum([Sales]) OVER (ParallelPeriod([Axis.X]))
Last Periods Method	Includes the current node and the n - 1 previous nodes (as defined by the each node value index). This can be used to calculate moving averages. Sum([Sales]) OVER (LastPeriods(3, [Axis.X]))/3

Note: All time period methods return the resulting visualization item subset, so the use of aggregation methods like average might not produce the expected result. See also Working with Time Hierarchies for more information about missing values in time series.