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 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(`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
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(`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
10/05/2012 Stationery 200
21/02/2012 Groceries 120
   21/02/2012
  Groceries
  100
  15/06/2012
  Groceries
  110
Output
Date Product Price Rank
21/02/2012 Groceries 120
1
21/02/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
21/02/2012 Groceries 120
  21/02/2012
  Groceries
  100
  15/06/2012
  Groceries
  110
Output
Date Product Price Dense Rank
21/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.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

    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
      Redefine the way you work
      with Zoho Workplace
      Start your free trial

        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 Sign now offers specialized one-on-one training for both administrators and developers.

            BOOK A SESSION





                        Still can't find what you're looking for?

                        Write to us:  support@zohoforms.com


                              



                            


                            Zoho Marketing Automation

                              Zoho Sheet Resources

                               




                                  Zoho Forms Resources


                                    Features

                                    Form Templates

                                    Customers

                                    Mobile Forms

                                    Pricing

                                    Integrations

                                    Blogs

                                    Ebooks

                                    Tips & Tricks

                                    Community

                                    Terms of Use

                                    Secure your business
                                    communication with Zoho Mail
                                    Get started


                                    Mail on the move with
                                    Zoho Mail mobile application
                                    Go Mobile

                                      Stay on top of your schedule
                                      at all times
                                      Get started


                                      Carry your calendar with you
                                      Anytime, anywhere
                                      Get started


                                        Latest Announcements


                                            Zoho Sign Resources

                                              What's new
                                              Help Guide
                                              Solutions Guide
                                              API
                                              Blogs
                                              FAQ's
                                              Legal Guide
                                              Forums
                                              Webinars

                                              Sign, Paperless!

                                              Sign and send business documents on the go!

                                              Get Started Now


                                                  Zoho SalesIQ Resources



                                                      Zoho TeamInbox Resources



                                                            Latest Feature Updates

                                                              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






                                                                            Latest Feature Updates


                                                                                Latest Feature Updates



                                                                                          • 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 ...

                                                                                          • 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 ...

                                                                                          • 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 ...

                                                                                          • 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
                                                                                          Workplace on mobile

                                                                                            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