Window functions to add formula columns

Window functions to add formula columns

A window function is used to perform calculations across a group of rows on a table. This 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(`Price`, 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(`Price ` ) is the same as rolling_sum(`Price ` , -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
ro
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(`Price` , 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(`Price`) is the same as rolling_avg(`Price`, -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(`Price` , 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(`Price `) is the same as rolling_min(`Price`, -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(`Price` , 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 Stationery 120
120
12/05/2012 Stationery 100 120
15/06/2012 Groceries 110 110
   22/01/2012
   Groceries
   200
   200
   10/04/2012
   Groceries
   110
   200
Note : Optional parameters can be skipped and the default values will be auto-applied. For example: rolling_max(`Price `) is the same as rolling_max(`Price`, -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(`Price` , 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(` Price`) is the same as rolling_stddev(`Price`, -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(`Price` , 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(` Price`) is the same as rolling_variance(`Price`, -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(`Price` , 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(`Price `) is the same as rolling_count(`Price`, -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
2
12/05/2012 Groceries 110 3
15/06/2012 Groceries 110
2
  22 /01/2012
  Stationery
   200
   2
  10/04/2012
  Stationery
   100
   2


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(`Price`, 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(`Price`, false) is the same as fill(`Price`, false, -1, 0).



Lag

lag ( col1 , rowsbefore )

Returns the value at a specified number of rows preceding the current row from a window of rows 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

Specifies the number of rows before the current row.
Example
Function
Group rows by
lag(`Price` , 1)
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 from a window of rows 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(`Price` , 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
22/01/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
2
15/06/2012 Groceries 110 3
   22/01/2012
   Stationery
   200
   1
   10/04/2012
   Stationery
   100
   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
22/01/2012 Stationery 200
21/02/2012 Groceries 120
  12/05/2012
  Groceries
  100
  15/06/2012
  Groceries
  110
Output
Date Product Price Dense Rank
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


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.333333333333333
12/05/2012 Groceries 100
0.666666666666667
15/06/2012 Groceries 110 1
22/01/2012
Stationery
200
0.5
10/04/2012
Stationery
100
1

    Redefine the way you work
    with Zoho Workplace

      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


          Get started with Zoho Sign

          in a few quick steps!

          Download Help Guide





                       
                      Secure your business
                      communication with Zoho Mail


                      Mail on the move with
                      Zoho Mail mobile application

                        Stay on top of your schedule
                        at all times


                        Carry your calendar with you
                        Anytime, anywhere




                                Zoho Sign Resources

                                  Sign, Paperless!

                                  Sign and send business documents on the go!

                                  Get Started Now


                                      Zoho SalesIQ Resources



                                          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

                                                                            • Add formula

                                                                              You can create new columns and customize them using the Formula column transform. Zoho DataPrep offers a variety of functions to suit your needs. Click here to know more about the functions. To create a formula column 1. Click on the Transform menu ...
                                                                            • Window functions to add formula columns

                                                                              A window function is used to perform calculations across a group of rows on a dataset. 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 ...
                                                                            • 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 using the Create workspace option. Alternatively, you can select an existing workspace ...
                                                                            Wherever you are is as good as
                                                                            your workplace

                                                                              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