Series functions

Series functions compare each row with the rows around it, such as the change in daily event counts. Use them to calculate running totals, moving averages, and period-over-period changes. Before you start, review the NetScript language reference for formula syntax.

A series is a set of rows in a fixed order. Declare a series with series along, then apply a series function to it.

Use the series along operation to put your data in order, so series functions know which row comes before another. The collation is the property that sets the order, such as a date. The partition groups rows into separate series, such as one series for each user. Analytics orders the rows within each partition.

The series along operation takes the following parts:

When you omit foreach, all rows start in one partition. Later slices with by add their properties to the partition.

Function

Data type – input type

Example

Result

Use the lag function to get the input value from the previous row in the partition. Use it to compare each value with the one before it, such as the change in daily event counts. For the first row, the result is null. Ensure your input is a series.

To look further back, add an integer offset as the second argument. The default offset is 1. For example, lag(x, 7) returns the value from seven rows earlier.

Function

Data type – input type

Example

Result

Use the lead function to get the input value from the next row in the partition. Use it to compare each value with the one after it. For the last row, the result is null. Ensure your input is a series.

To look further ahead, add an integer offset as the second argument. The default offset is 1. The shift function is an alias of lead and works the same way.

Function

Data type – input type

Example

Result

Use the rank function to number each row within its partition in collation order, starting at 1. Each row gets a unique rank, and Analytics breaks ties in an arbitrary order. Ensure your input is a series.

Function

Data type – number

Example

Result

Use the point_index function to number each row within its partition, in the order of the series. The point_index function is an alias of the rank function and returns the same result. Ensure your input is a series.

Function

Data type – number

Example

Result

Use the collect operation to aggregate a series within each partition, such as the last page each user viewed. Follow collect with an aggregate function, such as sum, or with one of the following options:

The first and last options skip rows with a null collation value when a row with a known value exists. The first and last aggregations are valid only after collect. Ensure your input is a series.

Function

Data type – input type

Example

Result

Use the cumulative_count function to count rows from the start of the partition through the current row. The function counts only rows where the input is not null. Ensure your input is a series.

Function

Data type – number

Example

Result

Use the cumulative_sum function to calculate a running total, such as the total number of events to date. For each row, the function adds the input values from the start of the partition through the current row. The input must be a number. Ensure your input is a series.

Function

Data type – number

Example

Result

Use the cumulative_avg function to calculate a running average, such as the average daily event count to date. For each row, the function averages the input values from the start of the partition through the current row. The input must be a number. Ensure your input is a series.

Function

Data type – number

Example

Result

Use the cumulative_stddev_pop function to calculate a running population standard deviation. For each row, the function covers the input values from the start of the partition through the current row. The input must be a number. Ensure your input is a series.

Function

Data type – number

Example

Result

Use the cumulative_corr function to calculate a running correlation between two numeric series. For each row, the function covers the values from the start of the partition through the current row. Ensure both inputs are series.

Function

Data type – number

Example

Result

Use the rolling_sum function to add up values in a sliding window of rows, such as a seven-day total. The window covers the current row, plus ROWS_BEFORE rows before it and ROWS_AFTER rows after it. Enter each row count as a whole number, such as 6 or 0. The input must be a number. Ensure your input is a series.

Function

Data type – number

Example

Result

Use the rolling_avg function to calculate a moving average over a sliding window of rows, such as a seven-day average. The window covers the current row, plus ROWS_BEFORE rows before it and ROWS_AFTER rows after it. Enter each row count as a whole number, such as 6 or 0. The input must be a number. Ensure your input is a series.

Function

Data type – number

Example

Result

Use the first_where function to find the input value at the first earlier row where a condition is true. For example, find the event count on the first day with more than 100 events. The function checks only rows before the current row and returns null when no row matches. Ensure your input is a series.

Function

Data type – input type

Example

Result

Use the last_where function to find the input value at the most recent earlier row where a condition is true. For example, find the event count on the last day with more than 100 events. The function checks only rows before the current row and returns null when no row matches. Ensure your input is a series.

Function

Data type – input type

Example

Result

Use the next_where function to find the input value at the next later row where a condition is true. For example, find the event count on the next day with more than 100 events. The function checks only rows after the current row and returns null when no row matches. Ensure your input is a series.

Function

Data type – input type

Example

Result