Window functions to add formula columns

A window function performs calculations across a group of rows in a table, called a window. You can use window functions to perform summations and calculations based on a rolling window of data, relative to the current row. Unlike a normal aggregate function, which returns a single output for the rows it is applied on, a window function retains every row in its result.

Zoho DataPrep provides 315 built-in functions in total. This page lists the 16 window functions. The remaining 299 functions are available in the Add formula transform - see Functions available to add formula columns.

Learn more about window functions with an example from this video.

List of window functions in DataPrep

Rolling Sum

rolling_sum(col1,rowsbefore,rowsafter)

Returns the rolling sum value from a window of rows, consisting of a number of rows before and after the current row.

Parameters

Name

Description

col1

Decimal

Specifies the source column. This parameter is mandatory and must be a numeric value from a column or an expression that returns a number.

rowsbefore

Number

[Optional] Specifies the number of rows before the current row. The default value is -1

rowsafter

Number

[Optional] Specifies the number of rows after the current row. The default value is 0

Example

Function	Sort rows by	Group rows by
rolling_sum('Sales', 1, 1)	Date	Product

Input

Date	Product	Price
10/04/2012	Stationery	100
22/01/2012	Stationery	200
21/02/2012	Groceries	120
12/05/2012	Groceries	100
15/06/2012	Groceries	110

Output

Date	Product	Price	Rolling Sum
21/02/2012	Groceries	120	220
12/05/2012	Groceries	100	330
15/06/2012	Groceries	110	210
22/01/2012	Stationery	200	300
10/04/2012	Stationery	100	300

Note: Optional parameters can be skipped and the default values will be auto-applied. For example: rolling_sum(`col1`) is the same as rolling_sum(`col1`, -1, 0).

Rolling Average

rolling_avg(col1,rowsbefore,rowsafter)

Returns the rolling average value from a window of rows consisting of a number of rows before and after the current row.

Parameters

Name

Description

col1

Decimal

Specifies the source column. This parameter is mandatory and must be a numeric value from a column or an expression that returns a number.

rowsbefore

Number

[Optional] Specifies the number of rows before the current row. The default value is -1 which includes all the rows before the current row.

rowsafter

Number

[Optional] Specifies the number of rows after the current row. The default value is 0 which includes the current row.

Example

Function	Sort rows by	Group rows by
rolling_avg(`Sales` , 1 , 1)	Date	Product

Input

Date	Product	Price
10/04/2012	Stationery	100
22/01/2012	Stationery	200
21/02/2012	Groceries	120
12/05/2012	Groceries	100
15/06/2012	Groceries	110

Output

Date	Product	Price	Rolling Avg
21/02/2012	Groceries	120	110
12/05/2012	Groceries	100	110
15/06/2012	Groceries	110	105
22/01/2012	Stationery	200	150
10/04/2012	Stationery	100	150

Note: Optional parameters can be skipped and the default values will be auto-applied. For example: rolling_avg(`col1`) is the same as rolling_avg(`col1`, -1, 0).

Rolling Minimum

rolling_min(col1,rowsbefore,rowsafter)

Returns the rolling minimum value from a window of rows consisting of a number of rows before and after the current row

Parameters

Name

Description

col1

Decimal

Specifies the source column. This parameter is mandatory and must be a numeric value from a column or an expression that returns a number.

rowsbefore

Number

[Optional] Specifies the number of rows before the current row. The default value is -1 which includes all the rows before the current row.

rowsafter

Number

[Optional] Specifies the number of rows after the current row. The default value is 0 which includes the current row.

Example

Function	Sort rows by	Group rows by
rolling_min(`Sales` , 1 , 1)	Date	Product

Input

Date	Product	Price
10/04/2012	Stationery	100
22/01/2012	Stationery	200
21/02/2012	Groceries	120
12/05/2012	Groceries	100
15/06/2012	Groceries	110

Output

Date	Product	Price	Rolling Min
21/02/2012	Groceries	120	100
12/05/2012	Groceries	100	100
15/06/2012	Groceries	110	100
22/01/2012	Stationery	200	100
10/04/2012	Stationery	100	100

Note: Optional parameters can be skipped and the default values will be auto-applied. For example: rolling_min(`col1`) is the same as rolling_min(`col1`, -1, 0).

Rolling Maximum

rolling_max(col1,rowsbefore,rowsafter)

Returns the rolling maximum value from a window of rows consisting of a number of rows before and after the current row.

Parameters

Name

Description

col1

Decimal

Specifies the source column. This parameter is mandatory and must be a numeric value from a column or an expression that returns a number.

rowsbefore

Number

[Optional] Specifies the number of rows before the current row. The default value is -1 which includes all the rows before the current row.

rowsafter

Number

[Optional] Specifies the number of rows after the current row. The default value is 0 which includes the current row.

Example

Function	Sort rows by	Group rows by
rolling_max(`Sales` , 1 , 1)	Date	Product

Input

Date	Product	Price
10/04/2012	Stationery	100
22/01/2012	Stationery	200
21/02/2012	Groceries	120
12/05/2012	Groceries	100
15/06/2012	Groceries	110

Output

Date	Product	Price	Rolling Max
21/02/2012	Groceries	120	120
12/05/2012	Groceries	100	120
15/06/2012	Groceries	110	110
22/01/2012	Stationery	200	200
10/04/2012	Stationery	100	200

Note: Optional parameters can be skipped and the default values will be auto-applied. For example: rolling_max(`col1`) is the same as rolling_max(`col1`, -1, 0).

Rolling Standard Deviation

rolling_stddev(col1,rowsbefore,rowsafter)

Returns the rolling standard deviation value from a window of rows consisting of a number of rows before and after the current row.

Parameters

Name

Description

col1

Decimal

Specifies the source column. This parameter is mandatory and must be a numeric value from a column or an expression that returns a number.

rowsbefore

Number

[Optional] Specifies the number of rows before the current row. The default value is -1 which includes all the rows before the current row.

rowsafter

Number

[Optional] Specifies the number of rows after the current row. The default value is 0 which includes the current row.

Example

Function	Sort rows by	Group rows by
rolling_stddev(`Sales` , 1 , 1)	Date	Product

Input

Date	Product	Price
10/04/2012	Stationery	100
22/01/2012	Stationery	200
21/02/2012	Groceries	120
12/05/2012	Groceries	100
15/06/2012	Groceries	110

Output

Date	Product	Price	Rolling Stddev
21/02/2012	Groceries	120	10
12/05/2012	Groceries	100	8.16496580927726
15/06/2012	Groceries	110	5
22/01/2012	Stationery	200	50
10/04/2012	Stationery	100	50

Note: Optional parameters can be skipped and the default values will be auto-applied. For example: rolling_stddev(`col1`) is the same as rolling_stddev(`col1`, -1, 0).

Rolling Variance

rolling_variance(col1,rowsbefore,rowsafter)

Returns the rolling variance value from a window of rows consisting of a number of rows before and after the current row.

Parameters

Name

Description

col1

Decimal

Specifies the source column. This parameter is mandatory and must be a numeric value from a column or an expression that returns a number.

rowsbefore

Number

[Optional] Specifies the number of rows before the current row. The default value is -1 which includes all the rows before the current row.

rowsafter

Number

[Optional] Specifies the number of rows after the current row. The default value is 0 which includes the current row.

Example

Function	Sort rows by	Group rows by
rolling_variance(`Sales` , 1 , 1)	Date	Product

Input

Date	Product	Price
10/04/2012	Stationery	100
22/01/2012	Stationery	200
21/02/2012	Groceries	120
12/05/2012	Groceries	100
15/06/2012	Groceries	110

Output

Date	Product	Price	Rolling Variance
21/02/2012	Groceries	120	100
12/05/2012	Groceries	100	66.66666666666667
15/06/2012	Groceries	110	25
22/01/2012	Stationery	200	2500
10/04/2012	Stationery	100	2500

Note: Optional parameters can be skipped and the default values will be auto-applied. For example: rolling_variance(`col1`) is the same as rolling_variance(`col1`, -1, 0).

Rolling Count

rolling_count(col1,rowsbefore,rowsafter)

Returns the rolling count values (that are not null) from a window of rows consisting of a number of rows before and after the current row.

Parameters

Name

Description

col1

Decimal

Specifies the source column. This parameter is mandatory and must be a numeric value from a column or an expression that returns a number.

rowsbefore

Number

[Optional] Specifies the number of rows before the current row. The default value is -1 which includes all the rows before the current row.

rowsafter

Number

[Optional] Specifies the number of rows after the current row. The default value is 0 which includes the current row.

Example

Function	Sort rows by	Group rows by
rolling_count(`Sales` , 1 , 1)	Date	Product

Input

Date	Product	Price
10/04/2012	Stationery	100
22/01/2012	Stationery	200
21/02/2012	Groceries	120
12/05/2012	Groceries	100
15/06/2012	Groceries	110

Output

Date	Product	Price	Rolling Count
21/02/2012	Groceries	120	2
12/05/2012	Groceries	100	3
15/06/2012	Groceries	110	2
22/01/2012	Stationery	200	2
10/04/2012	Stationery	100	2

Note: Optional parameters can be skipped and the default values will be auto-applied. For example: rolling_count(`col1`) is the same as rolling_count(`col1`, -1, 0).

Row Number

row_number()

Returns the row number from a window of rows based on the sort by and group by conditions.

Example

Function	Sort rows by	Group rows by
row_number()	Date	Product

Input

Date	Product	Price
10/04/2012	Stationery	100
22/01/2012	Stationery	200
21/02/2012	Groceries	120
12/05/2012	Groceries	100
15/06/2012	Groceries	110

Output

Date	Product	Price	Row Number
21/02/2012	Groceries	120	1
12/05/2012	Groceries	100	2
15/06/2012	Groceries	110	3
22/01/2012	Stationery	200	1
10/04/2012	Stationery	100	2

Fill

fill(col1,fillEmpty,rowsbefore,rowsafter)

Returns the values from the column with empty cells filled by the closest non-empty value from the preceding rows within the window of rows consisting of a number of rows before and after the current row.

Parameters

Name	Description
col1 Decimal	Specifies the source column. This parameter is mandatory and must be a numeric value from a column or an expression that returns a number.
fillEmpty Boolean	Specify true if the empty rows should be filled, false otherwise.
rowsbefore Number	[Optional] Specifies the number of rows before the current row. The default value is -1 which includes all the rows before the current row.
rowsafter Number	[Optional] Specifies the number of rows after the current row. The default value is 0 which includes the current row.

Example

Function	Sort rows by	Group rows by
fill(`Sales`, false, 1 , 1)	Date	Product

Input

Date	Product	Price
10/04/2012	Stationery	100
22/01/2012	Groceries
21/02/2012	Groceries
12/05/2012	Groceries	120
15/06/2012	Groceries	120

Output

Date	Product	Price	Fill
22/01/2012	Groceries
21/02/2012	Groceries		120
12/05/2012	Groceries	120	120
15/06/2012	Groceries	120	120
10/04/2012	Stationery	100	100

Note: Optional parameters can be skipped and the default values will be auto-applied. For example: fill(`col1`, `fillEmpty`) is the same as fill(`col1`, `fillEmpty`, -1, 0).

Lag

lag(col1,rowsbefore)

Returns the value at a specified number of rows preceding the current row in the column.

Parameters

Name	Description
col1 Text	Specifies the source column. This parameter can be a column of any datatype.
rowsbefore Number	Specifies the number of rows before the current row.

Name

Description

col1

Text

Specifies the source column. This parameter can be a column of any datatype.

rowsbefore

Number

Specifies the number of rows before the current row.

Example

Function	Sort rows by	Group rows by
lag(`Sales` , 1)	Date	Product

Input

Date	Product	Price
10/04/2012	Stationery	100
22/01/2012	Stationery	200
21/02/2012	Groceries	120
12/05/2012	Groceries	100
15/06/2012	Groceries	110

Output

Date	Product	Price	Lag
21/02/2012	Groceries	120
12/05/2012	Groceries	100	120
15/06/2012	Groceries	110	100
22/01/2012	Stationery	200
10/04/2012	Stationery	100	200

Lead

lead(col1,rowsafter)

Returns the value at a specified number of rows after the current row in the column.

Parameters

Name	Description
col1 Text	Specifies the source column. It is mandatory and can be of a string type column.
rowsafter Number	Specifies the number of rows after the current row.

Name

Description

col1

Text

Specifies the source column. It is mandatory and can be of a string type column.

rowsafter

Number

Specifies the number of rows after the current row.

Example

Function	Sort rows by	Group rows by
lead(`Sales` , 1)	Date	Product

Input

Date	Product	Price
10/04/2012	Stationery	100
22/01/2012	Stationery	200
21/02/2012	Groceries	120
12/05/2012	Groceries	100
15/06/2012	Groceries	110

Output

Date	Product	Price	Lead
21/02/2012	Groceries	120	100
12/05/2012	Groceries	100	110
15/06/2012	Groceries	110
22/01/2012	Stationery	200	100
10/04/2012	Stationery	100

Rank

rank()

Returns the ranking from a window of rows based on the sort by and group by conditions. If there is a tie, the subsequent number of ranks are skipped in the following rows.

Example

Function	Sort rows by	Group rows by
rank()	Date	Product

Input

Date	Product	Price
10/04/2012	Stationery	100
10/05/2012	Stationery	200
21/02/2012	Groceries	120
12/05/2012	Groceries	100
15/06/2012	Groceries	110

Output

Date	Product	Price	Rank
21/02/2012	Groceries	120	1
12/05/2012	Groceries	100	1
15/06/2012	Groceries	110	3
10/04/2012	Stationery	100	1
10/05/2012	Stationery	200	2

Dense Rank

dense_rank()

Returns the dense ranking from a window of rows based on the sort by and group by conditions. If there is a tie, there is no rank that is skipped.

Example

Function	Sort rows by	Group rows by
dense_rank()	Date	Product

Input

Date	Product	Price
10/04/2012	Stationery	100
10/05/2012	Stationery	200
20/02/2012	Groceries	120
21/02/2012	Groceries	100
15/06/2012	Groceries	110

Output

Date	Product	Price	Dense Rank
20/02/2012	Groceries	120	1
21/02/2012	Groceries	100	1
15/06/2012	Groceries	110	2
10/04/2012	Stationery	100	1
10/05/2012	Stationery	200	2

Cumulative distribution

cumulative_distribution()

Returns the cumulative distribution values from a window of rows.

Example

Function	Sort rows by	Group rows by
cumulative_distribution()	Date	Product

Input

Date	Product	Price
10/04/2012	Stationery	100
22/01/2012	Stationery	200
21/02/2012	Groceries	120
12/05/2012	Groceries	100
15/06/2012	Groceries	110

Output

Date	Product	Price	cumulative Distribution
21/02/2012	Groceries	120	0.3333
12/05/2012	Groceries	100	0.6667
15/06/2012	Groceries	110	1
22/01/2012	Stationery	200	0.5
10/04/2012	Stationery	100	1

First Value

first(value,ignoreNulls,default)

Returns the first value of an expression for a group of rows. If isIgnoreNull is true, null values are skipped. A default value can be used when all values are null.

Parameters

Name

Description

value

Text

Specifies the source column from which the first value will be retrieved.

ignoreNulls

Number

Optional. Set to 1 to skip nulls and return the first non-null value. Default is 0.

default

Text

Optional. Specifies a default value to return if all values are null.

Example

Function	Sort rows by	Group rows by
first('Price')	Date	Product

Input

Date	Product	Price
10/04/2012	Stationery	100
22/01/2012	Stationery	NULL
21/02/2012	Groceries	NULL
12/05/2012	Groceries	100
15/06/2012	Groceries	110

Output

Date	Product	Price	First Value
10/04/2012	Stationery	100	100
22/01/2012	Stationery	NULL	100
21/02/2012	Groceries	NULL	100
12/05/2012	Groceries	100	100
15/06/2012	Groceries	110	100

Note: Optional parameters can be skipped and the default values will be auto-applied. For example: first(`value`) is the same as first(`value`, 0, '').

Last Value

last(value,ignoreNulls,default)

Returns the last value of an expression for a group of rows. If ignoreNulls is set to 1, null values are skipped. A default value can be used when all values are null.

Parameters

Name

Description

value

Text

Specifies the source column from which the last value will be retrieved.

ignoreNulls

Number

Optional. Set to 1 to skip nulls and return the last non-null value. Default is 0.

default

Text

Optional. Specifies a default value to return if all rows are null.

Example

Function	Sort rows by	Group rows by
last('Price')	Date	Product

Input

Date	Product	Price
10/04/2012	Stationery	100
22/01/2012	Stationery	200
21/02/2012	Groceries	120
12/05/2012	Groceries	100
15/06/2012	Groceries	110

Output

Date	Product	Price	Last Value
21/02/2012	Groceries	120	110
15/06/2012	Groceries	110	110
12/05/2012	Groceries	100	110
22/01/2012	Stationery	200	100
10/04/2012	Stationery	100	100

Note: Optional parameters can be skipped and the default values will be auto-applied. For example: last(`value`) is the same as last(`value`, 0, '').