Window functions to add formula columns

Window functions to add formula columns

A window function is used to perform calculations across a set of rows on a table. A set of rows is termed a window. You can use window functions to perform summations and calculations based on a rolling window of data, relative to the current row.  

In contrast to a normal aggregate function which is applied on a row or multiple rows to return a single output, the window function even when applied over a set of rows (a window), does not literally group all the rows in its output. It retains the rows in the result as shown in the image below.



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 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_sum('Sales', 1, 1)
Name
ID
Input
ID
Name
Sales
company2
sam
4
company1
ram
100
company1
ravi
11
Output
ID
Name
Sales
Rolling_sum
company1
ram
100
111
company1
ravi
11
115
company2
sam
4
15



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)
Name
ID
Input
ID
Name
Sales
company2
sam
4
company1
ram
100
company1
ravi
11
Output
ID
Name
Sales
Rolling_avg
company1
ram
100
55.5
company1
ravi
11
55.5
company2
sam
4
4


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)
Name
ID
Input
ID
Name
Sales
company2
sam
4
company1
ram
100
company1
ravi
11
Output
ID
Name
Sales
Rolling_min
company1
ram
100
11
company1
ravi
11
4
company2
sam
4
4


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)
Name
ID
Input
ID
Name
Sales
company2
sam
4
company1
ram
100
company1
ravi
11
Output
ID
Name
Sales
Rolling_max
company1
ram
100
100
company1
ravi
11
100
company2
sam
4
11


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)
Name
ID
Input
ID
Name
Sales
company2
sam
4
company1
ram
100
company1
ravi
11
Output
ID
Name
Sales
Rolling_stddev
company1
ram
100
44.5
company1
ravi
11
43.69846170697
company2
sam
4
3.5



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)
Name
ID
Input
ID
Name
Sales
company2
sam
4
company1
ram
100
company1
ravi
11
Output
ID
Name
Sales
Rolling_variance
company1
ram
100
1980.25
company1
ravi
11
1980.25
company2
sam
4
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)
Name
ID
Input
ID
Name
Sales
company2
sam
4
company1
ram
100
company1
ravi
11
Output
ID
Name
Sales
Rolling_count
company1
ram
100
2
company1
ravi
11
2
company2
sam
4
1


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()
Name
ID
Input
ID
Name
Sales
company2
sam
4
company1
ram
100
company1
ravi
11
Output
ID
Name
Sales
Row_number
company1
ram
100
1
company1
ravi
11
2
company2
sam
4
1


Fill

fill(col1,ignoreEmpty,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.

ignoreEmpty

Boolean

Specify true if the empty rows should be ignored or 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)
Name
ID
Input
ID
Name
Sales
company2
sam
4
company1
ram
100
company1
ravi

Output
ID
Name
Sales
Fill
company1
ram
100
100
company1
ravi

100
company2
sam
4
4


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. It is mandatory and can be of a string type column.

rowsbefore

Number

[Optional] Specifies the number of rows before the current row.
Example
Function
Sort rows by
Group rows by
lag(`Sales` , 1)
Name
ID
Input
ID
Name
Sales
company2
sam
4
company1
ram
100
company1
ravi
3
Output
ID
Name
Sales
Lag
company1
ram
100

company1
ravi
3
100
company2
sam
4
3


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.
Example
Function
Sort rows by
Group rows by
lead(`Sales` , 1)
Name
ID
Input
ID
Name
Sales
company2
sam
4
company1
ram
100
company1
ravi
3
Output
ID
Name
Sales
Lead
company1
ram
100
3
company1
ravi
3
4
company2
sam
4



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()
Name
ID
Input
ID
Name
Sales
company2
sam
4
company1
ram
100
company1
ravi
100
Output
ID
Name
Sales
Rank
company1
ram
100
1
company1
ravi
100
1
company2
sam
4
3



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()
Name
ID
Input
ID
Name
Sales
company2
sam
4
company1
ram
100
company1
ravi
100
Output
ID
Name
Sales
Dense Rank
company1
ram
100
1
company1
ravi
100
2
company2
sam
4
1


Cumulative Distribution

cumulative_distribution()

Returns the cumulative distribution values from a window of rows.

Example
Function
Sort rows by
Group rows by
cumulative_distribution()
Name
ID
Input
ID
Name
Sales
company2
sam
4
company1
ram
100
company1
ravi
11
Output
ID
Name
Sales
Cumulative Distribution
company1
ram
100
0.5
company1
ravi
11
0.66666667
company2
sam
4
1.0


    Zoho DataPrep Personalized Demo

    If you'd like a personalized walk-through of our data preparation tool, please request a demo and we'll be happy to show you how to get the best out of Zoho DataPrep.

    Zoho CRM Training

      Create, share, and deliver

      beautiful slides from anywhere.

      Get Started Now





              Zoho CRM Training Programs

              Learn how to use the best tools for sales force automation and better customer engagement from Zoho's implementation specialists.

              Zoho CRM Training


                  Zoho TeamInbox Resources




                            Zoho DataPrep Resources

                              Zoho DataPrep Demo

                              Get a personalized demo or POC

                              REGISTER NOW


                                Design. Discuss. Deliver.

                                Create visually engaging stories with Zoho Show.

                                Get Started Now











                                                      • Related Articles

                                                      • Functions available to add formula columns

                                                        Abs abs(num) Returns the absolute value of the numeric value. Parameters Name Description num Decimal Parameter must be a numeric value from a column or an expression that returns a number. Example Function Result abs(-1) 1 Div ...
                                                      • Add prefix or suffix

                                                        DataPrep allows you to add prefixes and suffixes to your data. To add a prefix or a suffix to a column: 1. Right-click on a column and select the Add Prefix or Suffix option from the context menu. 2. Enter the prefix, suffix, or both in the text ...
                                                      • Create workspace and add new dataset

                                                        To import data, you need to create a new workspace, or open an existing workspace and add a dataset. To import data:  1. Create a new workspace from the home page. Alternatively, you can select an existing workspace if you have one, and create ...
                                                      • Duplicate Column

                                                        Zoho DataPrep allows you to duplicate columns present in your dataset. To duplicate columns 1. Right-click on a column header and select the Duplicate columns option from the context menu. 2. Select the columns from the Columns to duplicate dropdown. ...
                                                      • Pivot

                                                        Pivot Table distributes data for easy consumption. It spreads out the data in long, winding tables by converting categories into columns. A pivot can be created by selecting the Column, Row, and Data fields.  To apply the pivot transform: 1. Click ...

                                                      Resources

                                                      Videos

                                                      Watch comprehensive videos on features and other important topics that will help you master Zoho CRM.



                                                      eBooks

                                                      Download free eBooks and access a range of topics to get deeper insight on successfully using Zoho CRM.



                                                      Webinars

                                                      Sign up for our webinars and learn the Zoho CRM basics, from customization to sales force automation and more.



                                                      CRM Tips

                                                      Make the most of Zoho CRM with these useful tips.



                                                        Zoho Show Resources