General Purpose Functions
The Analytic functions described in this section can provide a great deal of flexibility and utility for many kinds of applications.
Aggregate Column
Provides the cumulative sum of the numbers in a column of data from top to bottom returned in a column of equal length. The line-by-line aggregate may also be displayed as a percentage of the total sum of all the numbers.
Parameter Set
| Parameters | Description |
| Column | Column on which to perform the aggregate operation. Column numbers begin with zero |
| Option | Display result as cumulative SUM or PERCENTAGE |
| Include Original Data | If “Yes” include the original dataset in the result table |
Applications
Can provide running totals or percentages of periodic data sets like income over time, expenses over time, etc. Many other uses.
Data Sample
| Input | Parameter | Result | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Column = 1Option = SUM
Include Original Data = Yes |
|
Chart Sample
Grade Column
This is a very useful function that calculates the relative importance or weight of each number in a data set with respect to the other members of the data set. It is calculated by percentage of the total sum, by probability (normalized so that total sum = 1), by percentile (normalized so that the maximum value = 100) or by rank.
Parameter Set
| Parameters | Description |
| Column number | Column on which to perform the grade column operation. Column numbers begin with zero |
| Option | PERCENTAGE, PROBABILITY, PERCENTILE or RANK |
| Include Original Data | If “Yes” include the original dataset in the result table |
Applications
Compare individual’s performance against their peers (Jolene S. is responsible for 50% of the 5-person department’s output), compare assets in a financial portfolio, product market share, etc. Many other uses.
Data Sample
| Input | Parameter | Result | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Column = 1Option = PERCENTAGE
Include Original Data = Yes |
|
Chart Sample
Delete Column
Utility function used to delete selected columns from the current data set.
Parameter Set
| Parameters | Description |
| Column To Delete | FIRST, LAST, OTHER |
| Column Index | Comma separated list of column indices if Column To Delete choice is OTHER |
Applications
This can be used to clean up the data set for data presentation in a table, or when a variable number of columns are being used to populate a chart. It is similar to the DataFilter process module supplied with NetCharts Reporting Suite, but provides the additional ability to delete the LAST column.
Data Sample
| Input | Parameter | Result | ||||||||||||||||||||||||||||||||||||||||||
|
Column To Delete = OTHERColumn Index = 2 |
|
Delete Rows
Utility function used to delete selected rows from the current data set.
Parameter Set
| Parameters | Description |
| Row To Delete | FIRST, LAST, OTHER |
| Row Index | Comma separated list of column indices if Row To Delete choice is OTHER |
Applications
This can be used to clean up the data set for data presentation in a table, or when a variable number of rows are being used to populate a chart. It is similar to the DataFilter process module supplied with NetCharts Reporting Suite, but provides the additional ability to delete the LAST row.
Data Sample
| Input | Parameter | Result | ||||||||||||||||||||||||||||||||||||||||||||
|
Column To Delete = LASTRow Index =
|
|
Include Headers
Add new header row or replace existing data column headers with user-specified comma-separated list.
Parameter Set
| Parameters | Description |
| Column Action | MODIFY or ADD |
| Row Index | Column names, seperated by a comma |
Applications
Charts are often configured to use the top row of a data set as the legend items; this function allows the developer to supply user-friendly names
Data Sample
| Input | Parameter | Result | ||||||||||||||||||||||||||||||||||||||||
|
Column Action = MODIFY Column Names = User Id,Last Name,First Name, Quarterly Earnings |
|
Count Group Names
Group a column of strings or names by content and count the number of occurrences of each string. The result is returned in two columns listing each distinct string and the corresponding number of occurrences side by side. The columns are sorted using an ascending or descending count.
Parameter Set
| Parameters | Description |
| Column | Column on which to perform the group operation. Column numbers begin with zero |
| Option | Sort data by ascending (ASC) or descending (DESC) count |
| Include Original Data | If “Yes” include the original dataset in the result table |
Applications
Can be used to count the number of transactions on a given day, the number of event occurrences in a given time period, the number of support calls from a given customer, etc. Many other uses.
Data Sample
| Input | Parameter | Result | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Column = 1 Option = DESC Include Original Data = No |
|
Chart Sample
Sum Group Names
Group a column of strings or names by content. For each string, sum the corresponding numbers in another column. The result is returned in two columns listing each distinct string and the corresponding sum side by side. The columns are sorted by ascending or descending sum.
Parameter Set
| Parameters | Description |
| Column | Column used to group by string. Column numbers begin with zero |
| Column to Aggregate | Column used to sum numbers corresponding to content of first column. Column numbers begin with zero |
| Option | Sort data by ascending (ASC), NONE, or descending (DESC) count |
| Include Original Data | If “Yes” include the original dataset in the result table |
Applications
Use to add up all transactions on a given day, total the revenue for a given product/sales person/division, sum all the defects for a given process, etc. Many other uses.
Data Sample
| Input | Parameter | Result | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Column = 0 Column to Aggregate = 1 Sort = NO SORT Include Original Data = No |
|
Chart Sample
Sort 1 Column
Sorts a single column of numbers in ascending or descending order and return the result in a column of equal length. The rest of the data, if present, will not be modified.
Parameter Set
| Parameters | Description |
| Column number | Column on which to perform the sort operation. Column numbers begin with zero |
| Option | Specify ascending (ASC) or descending (DESC) sort |
| Include Original Data | If “Yes” include the original dataset in the result table |
Applications
Use when a sorted version of a given column is need in addition to the original set, perhaps for menu items, different data sets, etc.
Data Sample
| Input | Parameter | Result | |||||||||||||||||||||||||||||||||||
|
Column number = 1 Option = ASC Include Original Data = Yes |
|
Two Column Operation
Perform item by item addition, subtraction, multiplication or division with two columns of data of equal length and return the result in a column of the same length as the input columns.
Parameter Set
| Parameters | Description |
| Column 1 | First column on which to perform the two-column operation. Column numbers begin with zero |
| Column 2 | Second column on which to perform the two-column operation. Column numbers begin with zero |
| Operation | ADD, SUBTRACT, MULTIPLY or DIVIDE |
| Result Column Name | Name of result column |
| Include Original Data | If “Yes” include the original dataset in the result table |
Applications
Use to perform arithmetic between data in different columns.
Data Sample
| Input | Parameter | Result | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Column 1 =1 Column 2 =2 Operation = SUBTRACT Result Column Name = net Include Original Data = Yes |
|
Chart Sample
For this chart, we converted the cost of sale column to be a negative number using the Basic Column Math process module, which is part of the default process module set, before plotting.
