TIBCO Cloud™ Spotfire® Web Client User Guide

OVER functions

The OVER functions are used to determine how data should be sliced, for example, relative to time periods.

Note: The use of OVER functions is different for custom expressions and for calculated columns. In custom expressions you can include references to axes, whereas in calculated columns you can only refer to columns or fixed hierarchies.

For more information, see Using OVER statement to reference data slices and Using OVER statements in calculated columns.

Option Description
All Uses all the nodes in the referenced hierarchy. This can be useful when intersecting the current node with more than one hierarchy. For example, you can show the relative sales of different product categories for each month.

Examples of custom expressions:

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

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

Examples of calculated columns:

Sum([Sales]) OVER All([Date])

Max([Sales]) OVER Intersect([Category], All([Date]))

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

Example of a custom expression:

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

Examples of calculated columns:

Sum([Sales]) OVER AllNext([Date])

Max([Sales]) OVER Intersect([Category], AllNext([Date]))

AllPrevious Uses all nodes, including the current, from the start of the level. This can be used to calculate the cumulative sum.

Examples of custom expressions:

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

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

Examples of calculated columns:

Sum([Sales]) OVER AllPrevious([Date])

Max([Sales]) OVER Intersect([Category], AllPrevious([Date]))

FirstNode Selects the first node on the current level.

Example of a custom expression:

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

Example of a calculated column:

Sum([Sales]) - Sum([Sales]) OVER (FirstNode([Hierarchy.TimeHierarchy]))

Intersect Returns the intersected rows from nodes in different hierarchies. See also AllPrevious and All.

Example of a custom expression:

Intersect(Parent([Axis.X]), All([Axis.Color]), Parent([Axis.Rows]), ...)

Example of a calculated column:

Sum([Sales]) OVER Intersect([Category], AllPrevious([Date]))

LastNode Selects the last node on the current level.

Example of a custom expression:

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

Example of a calculated column:

Sum([Sales]) - Sum([Sales]) OVER (LastNode([Hierarchy.TimeHierarchy]))

LastPeriods Includes the current node and the n - 1 previous nodes. This can be used to calculate moving averages.

Example of a custom expression:

Sum([Sales]) OVER (LastPeriods(3, [Axis.X]))/3

Example of a calculated column:

Sum([Sales]) - Sum([Sales]) OVER (LastPeriods([Hierarchy.TimeHierarchy]))

NavigatePeriod Allows you to specify your own node navigation. The method has four arguments:

Arg1 is the hierarchy to navigate.

Arg2 is a string value specifying the level in the hierarchy that you should navigate up to.

Arg3 is an integer specifying the number of steps to move sideways in the hierarchy at the level specified by Arg1.

Arg4 is optional and determines the level in the hierarchy to move down to. This argument can be omitted in which case a navigation to the leaf level is made.

Examples of custom expressions:

Sum([Sales]) OVER NavigatePeriod([Axis.X], "Year", -1)

Sum([Value]) OVER NavigatePeriod([Axis.X], "Year", 0, 0)

Sum([Sales]) OVER NavigatePeriod([Axis.X], 2, -1, 2)

Example of a calculated column:

Avg([Sales]) OVER (NavigatePeriod([Hierarchy.TimeHierarchy],0,-10)) - Avg([Sales])OVER (NavigatePeriod([Hierarchy.TimeHierarchy],0,10))

Next 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.

Examples of custom expressions:

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

Count() OVER Next([Axis.X], 2)

Example of a calculated column:

Max([Sales]) OVER (Intersect([Category],Next([Year])))

NextPeriod Uses the next node which has the next value on the same level as the current node. 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.

Examples of custom expressions:

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

Count() OVER NextPeriod([Axis.X], 2)

Example of a calculated column:

Max([Sales]) OVER (Intersect([Category],NextPeriod([Year])))

ParallelPeriod Uses the previous parallel node with the same value on the same level as the current node. For example, this can be used to compare sales results for each month with the corresponding months the previous year.

Example of a custom expression:

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

Example of a calculated column:

Sum([Sales])-Sum([Sales]) OVER (ParallelPeriod([Hierarchy.TimeHierarchy]))

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

Examples of custom expressions:

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

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

Avg([Sales]) OVER Parent([Axis.X])

Example of a calculated column:

Sum([Sales]) OVER (Parent([Hierarchy.TimeHierarchy]))

Previous 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.

Examples of custom expressions:

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

Count() OVER Previous([Axis.X], 2)

Example of a calculated column:

Max([Sales]) OVER (Intersect([Category],Previous([Year])))

PreviousPeriod Uses the previous node which has the previous value on the same level as the current node. 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.

Examples of custom expressions:

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

Count() OVER PreviousPeriod([Axis.X], 2)

Example of a calculated column:

Max([Sales]) OVER (Intersect([Category],PreviousPeriod([Year])))

See also Functions.
Copyright © Cloud Software Group, Inc. All rights reserved.