Returns the rolling sum value from a window of rows, consisting of a number of rows before and after the current row.
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.
|
Function
|
Sort rows by
|
Group rows by
|
---|---|---|
rolling_sum(`Price`, 1, 1)
|
Date | Product |
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 |
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
|
Returns the rolling average value from a window of rows consisting of a number of rows before and after the current row.
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.
|
Function
|
Sort rows by
|
Group rows by
|
---|---|---|
rolling_avg(`Price` , 1 , 1)
|
Date | Product |
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
|
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
|
Returns the rolling minimum value from a window of rows consisting of a number of rows before and after the current row
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.
|
Function
|
Sort rows by
|
Group rows by
|
---|---|---|
rolling_min(`Price` , 1 , 1)
|
Date | Product |
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
|
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
|
Returns the rolling maximum value from a window of rows consisting of a number of rows before and after the current row.
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.
|
Function
|
Sort rows by
|
Group rows by
|
---|---|---|
rolling_max(`Price` , 1 , 1)
|
Date | Product |
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
|
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
|
Returns the rolling standard deviation value from a window of rows consisting of a number of rows before and after the current row.
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.
|
Function
|
Sort rows by
|
Group rows by
|
---|---|---|
rolling_stddev(`Price` , 1 , 1)
|
Date | Product |
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
|
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
|
Returns the rolling variance value from a window of rows consisting of a number of rows before and after the current row.
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.
|
Function
|
Sort rows by
|
Group rows by
|
---|---|---|
rolling_variance(`Price` , 1 , 1)
|
Date | Product |
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
|
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
|
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.
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.
|
Function
|
Sort rows by
|
Group rows by
|
---|---|---|
rolling_count(`Price` , 1 , 1)
|
Date | Product |
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
|
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
|
Returns the row number from a window of rows based on the sort by and group by conditions.
Function
|
Sort rows by
|
Group rows by
|
---|---|---|
row_number()
|
Date | Product |
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
|
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
|
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.
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.
|
Function
|
Sort rows by
|
Group rows by
|
---|---|---|
fill(`Price`, false, 1 , 1)
|
Date | Product |
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
|
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
|
Returns the value at a specified number of rows preceding the current row from a window of rows in the column.
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.
|
Function
|
Group rows by
|
---|---|
lag(`Price` , 1)
|
Product |
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
|
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
|
Returns the value at a specified number of rows after the current row from a window of rows in the column.
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.
|
Function
|
Sort rows by
|
Group rows by
|
---|---|---|
lead(`Price` , 1)
|
Date | Product |
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
|
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
|
|
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.
Function
|
Sort rows by
|
Group rows by
|
---|---|---|
rank()
|
Date | Product |
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
|
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
|
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.
Function
|
Sort rows by
|
Group rows by
|
---|---|---|
dense_rank()
|
Date | Product |
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
|
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
|
Returns the cumulative distribution values from a window of rows.
Function
|
Sort rows by
|
Group rows by
|
---|---|---|
cumulative_distribution()
|
Date | Product |
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
|
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
|
Learn how to use the best tools for sales force automation and better customer engagement from Zoho's implementation specialists.
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.
Write to us: support@zohoforms.com