Window Functions

Spectre window functions operate on the data in the output window, not on the input data.

Return the expression using the detail records for the prior row. Enables cumulative calculations by using named calcs.
previous(expression : any, start : any) : any
This function is a helper for cumulative columns. It returns the value of the cell in the previous row on the same column in the output window, or the start value if there is no previous row.

For example:

`previous(calc("Units"), 0)` returns the Units calc from the previous row

calc "Cumulative Weight" `calc("Weight") + previous(calc("Cumulative Weight"), 0)` defines a cumulative weight column for a Dive window. The cumulative weight for the current row is the sum of the current weight column plus the cumulative weight value from the previous row.

calc "cum profit" `calc("profit") + previous(calc("cum profit"),0)` defines a cumulative profit column

calc "Cumulative Sales" `previous(calc("Cumulative Sales"), 0) + calc("Sales")` adds Sales to the last value of Cumulative Sales

NOTE:

  • The value for the first row is the start or initial value, which is usually zero. Specifying a start value is optional.
  • You can also use previous() with non-numeric expressions.
  • The previous() function requires two arguments.
  • Spectre 7.1 supports calculations using multiple distinct expressions involving previous().
Return the number of rows in the current window.
row_count() : integer
This function allows for a generalized expression of a mean calculation.

For example:

`row_count()` returns number of rows in current window

calc "mean weight" `(1/row_count())*average(value("weight"))` returns the mean average of the summary column for the current diving dimension

Return the row number in the current window of the cell being filled.
row_number() : integer

For example:

add "Lab ID" `concat("Lab_",lpad(string(row_number()),10,"0"))` generates a unique key in the data set

NOTE: In order to make row_number() work properly under text-input tags with multiple inputs while building in parallel, a serial build is performed instead.

Determine if a user property contains a specified value. This function is for use in access control scripts only—other script types can use parameters with default-from-user-property.
user_property_contains(property_name : string, value : string) : expression

For example:

`user_property_contains("State", "Ohio")`checks if the State user property contains Ohio

`user_property_contains("column_controlled", value("prop_column")) checks if the column_controlled user property contains the value in the cBase column prop_column

See also: