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.
Note: When working with in-db data you must always apply OVER expressions to the already aggregated data using the THEN keyword, because there is no row-level data available in that case. This expression structure can also improve the performance when working with in-memory data. See Using Expressions on Aggregated Data (the THEN Keyword) for more information.
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])) as Parent
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 as Parent
|
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])) as Next
Note that the next method will use the next, following node on the same level, without any regards to whether an expected node is missing. For example, if data for a month is missing as in the image below:
If the current node is 2016.Jan, in this figure, 'next' in this case will refer to 2016.Mar, since 2016.Feb is missing in the data. See NextPeriod method for a value-based reference instead. |
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])) as Previous
Note that the previous method will use the preceding node on the same level, without any regards to whether an expected node is missing. For example, if data for a month is missing as in the image below:
If the current node is 2016.Mar, in this figure, 'previous' in this case will refer to 2016.Jan, since 2016.Feb is missing in the data. See PreviousPeriod method for a value-based reference instead. |
AllMethod
|
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])) as All
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 as All
|
|
|
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])) as AllPrevious
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]))) as AllPrevious
Note: See the Intersect method for information on that method. |
AllNext 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 AllPrevious
method and the All method for examples
of how it works. It is also possible to write empty intersects: "OVER Intersect()". This is used in nested aggregations, when you want to dynamically intersect with all categorical axes in an aggregated visualization but not you do not want to intersect with anything else. If there are no categorical axes to intersect with (e.g., in calculated columns or in an unaggregated visualization), the OVER expression will simply be removed before the expression is executed in the data engine. |
FirstNode method |
Navigates to the first node in the current level on the specified axis. FirstNode([Axis.X]) |
LastNode Method |
Navigates to the last node in the current level on the specified axis. LastNode([Axis.X]) |
LastPeriods 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 as LastPeriods
|
Node period methods
Node 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. For example, they can be used to make more accurate calculations in time-based data where specific time periods are missing for some years.
Method |
Description |
NavigatePeriod Method |
This method allows you to specify your own node navigation. It allows you to select a subset of the underlying data corresponding to a hierarchy node reached by navigating up from the current node, then sideways and finally down along a path corresponding to that previously navigated up. If a corresponding node in the path cannot be found (e.g., if data for one month is missing) then no nodes will be selected and there will be no value for the current node The method can be called with three or four arguments: Arg1: The hierarchy to navigate, typically [Axis.X] or similar. Arg2: The name of the level in the hierarchy to move up to. For example, “Year”, provided that there is a Year>>Quarter>>Month hierarchy on the X-axis. This argument can also be specified with an integer denoting the number of steps to move to move up from the leaf level. Arg3: The number steps to move sideways in the hierarchy at the level specified by the first argument. For example, -1 moves to the previous node. Arg4: The name of the level in the hierarchy to move down to, or an integer specifying the number of steps to move down. This argument can be omitted in which case a navigation to the leaf level is made.
Examples, assuming that there is a Year>>Quarter>>Month hierarchy on the X-axis:
|
NextPeriod 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. For example, as seen in the image below, December 2015 will have a value, but December 2016 will not, since there is no data available for January 2017 in the analysis. Sum([Sales]) OVER (NextPeriod([Axis.X])) as NextPeriod
|
PreviousPeriod 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])) as PreviousPeriod
|
ParallelPeriod 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])) as ParallelPeriod
|
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.
Example: The difference between Previous and PreviousPeriod
'Previous' and 'PreviousPeriod' may seem similar, but there is a difference in how to use the two methods. PreviousPeriod is, like the other node navigation methods, expected to work on time hierarchies and will therefore expect to find the same nodes available for all of the different values.
'Previous' will compare the current node to the node to the left of it in the hierarchy, whereas 'PreviousPeriod' will compare the current node to the expected node in a time-based hierarchy. If a node is missing, no value will be calculated.
Consider the following data:
Year;Quarter;Sales
2016;Q1;10
2016;Q2;20
2016;Q3;5
2016;Q4;25
2017;Q1;30
2017;Q3;15
2017;Q4;5
It contains sales figures for the different quarters during two years.
Create a bar chart, with Year and Quarter on the X-axis. Notice that there is no data for Q2 2017, as shown in the first bar chart below.
Then, duplicate this visualization and, on the Y-axes of the new bar charts, use Sum([Sales]) - Sum([Sales]) OVER (Previous([Axis.X])) as Previous and Sum([Sales]) - Sum([Sales]) OVER (PreviousPeriod([Axis.X])) as PreviousPeriod correspondingly, to compare the two different functions.
When using Previous, you will get a bar for Q3 2017, because the Previous function will compare the sales in Q3 2017 to the sales in Q1 2017 (the node before the Q3 2017 node), and calculate the difference between those two values. However, when using PreviousPeriod, you will not get any bar for Q3 2017, because the node to compare with, Q2, is not available for that year, and no value can be calculated for Q3. (Q2 is expected to exist because there was a Q2 available in the data for 2016. If no Q2 was available there either, then Q1 would have been PreviousPeriod of Q3.) The difference is similar for Next and NextPeriod methods.
