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

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.

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.

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

What are the available dataset transforms?

How to apply pivot transform?

How to use filter transform?

Access your files securely from anywhere

Zoho CRM Training Programs

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

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.

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

Zoho Workerly Home
Forums
Connect With Us:

Zoho Recruit Home
Forums
Connect With Us:

Start tracking your website metrics today

Use PageSense to understand what your visitors are looking for, how they are behaving, and where they are facing problems on your website.

Ready to improve your website's performance?

Install the PageSense code snippet on your site in a matter of minutes and start collecting in-depth data about the website visitors to grow your business.

Track and measure every business goal

Set up goals in PageSense to measure every single action performed by visitors on your website like button or link clicks, form submissions, and page engagements.

Understand the customer journey on your website

Create funnels in PageSense to quickly see which pages visitors use to enter your website, where they navigate to next, and which pages they decide to leave without converting.

Visualize your visitor's behavior with color codes

Set up heatmaps in PageSense to see where users have clicked more, how far they've scrolled, and on which parts of a page they've spent the most time using color-coded patterns in reports.

Measure customer engagement with your web forms

Use form analytics in PageSense to see how people interact with different fields in your form, whether they complete the form successfully or not, and where exactly they drop out on your form.

Record real visitor interactions on your website

Use session recordings in PageSense to watch a video of all the visitor actions performed on your website including the pages they navigate, the buttons they click, the UX issues they face, and more.

Test and optimize webpages for better conversions

Run A/B or Split URL tests in PageSense to figure out which version of your web page works best for your business and results in the best conversion rate.

Give each customer a unique website experience

Use personalization in PageSense to deliver customized versions of your website for every individual customer based on their demographics, local weather, browsing history, and more.

Grow your business through customer feedback

Run polls on your website using PageSense to understand what your customers think about your products/services and what needs improvement on your site.

Send timely updates that customers love

Use web push notifications in PageSense to schedule and notify your customers about an upcoming flash sale, product releases, promotional coupons, and a lot more that can spark conversions on your website.

Advertise your business to website visitors

Use pop-ups in PageSense to instantly grab the attention of visitors by showing attractive signup offers, coupon code discounts, or email newsletters that can eventually convert them into subscribers.

Access more advanced settings in PageSense

Use PageSense's advanced features like creating mutually exclusive groups, enabling cross-domain tracking, configuring customized project JS, and more to get deeper insights about your website.

Try easy-to-use extensions

Download the PageSense extension app available for your web browser with a few clicks and start collecting all of your required website metrics in real time.

Discover your favorite integrations with PageSense

Get a deeper look at your website's data by seamlessly integrating PageSense with a host of popular third-party apps like Google Analytics, Mixpanel, Intercom, and more.

Quick Links	Workflow Automation	Data Collection
Web Forms	Enterprise	Begin Data Collection
Interactive Forms	Workplace	Data Collection App
Offline Forms	Customer Service	Accessible Forms
Digital Forms	Marketing	Forms for Small Business
HTML Forms	Education	Forms for Enterprise
Contact Forms	E-commerce	Forms for any business
Lead Generation Forms	Healthcare	Forms for Startups
Wordpress Forms	Customer onboarding	Order Forms for Small Business
No Code Forms	Construction
Free Forms	Travel
Prefill Forms	Non-Profit
Intake Forms	Legal
Form Designer	HR
Card Forms	Food
Assign Forms	Photography
Translate Forms	Real Estate
Electronic Forms
Notification Emails for Forms
Holiday Forms
Form to PDF
Email Forms CRM Forms Embeddable Forms Drag and Drop form builder HIPAA Forms Encrypted Forms

New to Zoho CRM Plus?

Deliver unforgettable customer experiences

ACCESS ZOHO CRM PLUS

New to Zoho CRM Plus?

Deliver unforgettable customer experiences

ACCESS ZOHO CRM PLUS

New to Zoho Marketing Plus?

Everything you need to run your marketing

ACCESS ZOHO MARKETING PLUS

New to Zoho Marketing Plus?

Everything you need to run your marketing

ACCESS ZOHO MARKETING PLUS

Zoho Pagesense Resources

You are currently viewing the help pages of Qntrl’s earlier version. Click here to view our latest version—Qntrl 3.0's help articles.

New to Zoho Survey?

Access Zoho Survey

Latest Feature Updates

Explore our Pricing & Plans

Zoho Survey Resources

Android Mobile Surveys

Insurvey Blogs

Insight Blogs

Tips & Tricks

New to Zoho Social?

Manage your brands on social media

Access Zoho Social

Zoho Desk Resources

Desk Community Learning Series
Digest
Functions
Meetups
Kbase
Resources
Glossary
Desk Marketplace
MVP Corner
Word of the Day

Zoho Sheet Resources

New to Zoho Forms?

Latest Feature Updates

Explore our Pricing & Plans

Zoho Forms Resources

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

New to Zoho Sign?

Zoho Sign Resources

Sign, Paperless!

Sign and send business documents on the go!

Get Started Now

Zoho SalesIQ Resources

New to Zoho TeamInbox?

Zoho TeamInbox Resources

Connect with us

New to Zoho ZeptoMail?

LEARN MORE

Latest Feature Updates

Zoho DataPrep Resources

New to Zoho DataPrep?

Access Zoho DataPrep

Zoho DataPrep Demo

Get a personalized demo or POC

Design. Discuss. Deliver.

Create visually engaging stories with Zoho Show.

Get Started Now

New to Zoho Workerly?

Access Zoho Workerly

New to Zoho Recruit?

Access Zoho Recruit

New to Zoho CRM?

Signup Now

Access Zoho CRM

Latest Feature Updates

New to Zoho Projects?

Access Zoho Projects

New to Zoho Sprints?

Access Zoho Sprints

New to Zoho Assist?

Access Zoho Assist

New to Bigin?

Signup Now

Access Bigin

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 ...
Duplicate Column
Zoho DataPrep allows you to duplicate columns present in your data. 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. 3. ...