Pivot
To apply the pivot transform
|
Data type |
Functions |
Advanced functions |
|
Number |
Normal |
Normal
Running total % of row % of row in group % of column % of column in group % of total Difference from % of difference from % of previous value Lookup value |
|
Running total |
||
|
Minimum |
||
|
Average |
||
|
Standard deviation |
||
|
Median |
||
|
Mode |
||
|
Percentile |
||
|
Variance |
||
|
Count |
||
|
Distinct count |
||
|
Text |
Count |
Normal Running total % of row % of row in group % of column % of column in group % of total Difference from % of difference from % of previous value Lookup value
|
|
Distinct count |
||
|
List |
Count |
Normal Running total % of row % of row in group % of column % of column in group % of total Difference from % of difference from % of previous value Lookup value |
|
Distinct count |
||
|
Map |
Count |
Normal Running total % of row % of row in group % of column % of column in group % of total Difference from % of difference from % of previous value Lookup value |
|
Distinct count |
||
|
Boolean |
Count |
Normal Running total % of row % of row in group % of column % of column in group % of total Difference from % of difference from % of previous value Lookup value |
|
Distinct count |
||
|
Date |
Count |
Normal Running total % of row % of row in group % of column % of column in group % of total Difference from % of difference from % of previous value Lookup value |
|
Maximum date |
||
|
Minimum date |
||
|
Distinct count |
Year Quarter and year Month and year Week and year Full date Date & time Quarter Month Week Day of the week Day of the month Hour of day |
Aggregate functions
| Function name | Description |
| Sum | Returns the sum of values from a numeric column. |
| Maximum | Return the maximum value from a numeric column. |
| Minimum | Returns the minimum value from a numeric column. |
| Average | Returns the average of all the values from a numeric column. |
| Standard deviation | Returns the standard deviation based on all values from a numeric column. |
| Median | Returns the middle value of the numeric column. |
| Mode | Returns the value that occurs the highest number of times in a numeric column. |
| Percentile | Returns the percentile of the column. The 50th percentile is configured by default. However, you can modify this value as you need. |
| Variance | Returns the variance of the numeric column. |
| Count | Returns the total number of values from a column. |
| Distinct count | Returns the total number of distinct values from a column. |
|
Maximum date
|
Returns the maximum date from a date and date-time column.
|
|
Minimum date
|
Returns the minimum date value from a date and date-time column.
|
Advanced functions
| Advanced function name | Description |
| Normal | Returns the value of the function applied over a numeric column. This option is selected by default. |
|
Running total
|
Returns the sum of sequence of numbers as a running total based on the selected Base field.
|
|
% of row
|
Returns the percentage of the total for each row. |
|
% of row in group
|
Returns the total value of each row within a group as a percentage.
|
|
% of column
|
Returns the percentage of the total for each column.
|
|
% of column in a group
|
Returns the total value of each row within a group as a percentage.
|
| % of total | Returns the percentage of the total of all the data. |
| Difference from | Returns the difference from the previous value based on the selected Base field. |
|
% of difference from
|
Returns the differences from the previous values as a percentage based on the Base field. |
| % of previous value | Returns the percentage of the previous value based on the selected Base field. |
|
Lookup
|
Returns the value from any of the previous, next, first or last cell based on the Base field selected. |
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.
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 Workerly Home
- Forums
- Connect With Us:
- Zoho Recruit Home
- Forums
- Connect With Us:
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.
Zoho Sheet Resources
Zoho Forms Resources
Zoho Sign Resources
Sign, Paperless!
Zoho TeamInbox Resources
Zoho DataPrep Resources
Design. Discuss. Deliver.
Related Articles
Dataset transforms
Dataset transform consists of combining and transforming datasets to suit your needs. You can apply the following transforms in a dataset. Deduplicate Row-wise Column-wise Join Append Pivot Unpivot Calculated columns Derive dataset Mark as personal ...User roles and permissions in Zoho DataPrep
Zoho DataPrep has roles for users based on their entity permissions. In other words, the user roles in DataPrep is based on the user's access to each entity. There are two other roles—Account admin and Organization admin—that are not entity-based. ...Zoho DataPrep Pricing FAQ
1.1 What are "rows processed"? A row can be defined in the context of a dataset. In simple terms, a dataset consists of rows (records) and columns (fields). Each row in a dataset represents a set of related data and has the same structure. For ...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 ...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 ...