Applying advanced functions
Advanced functions are tools that perform specific, useful tasks in Reports. You can use advanced functions to perform complex calculations or operations on fields.
How it works
An advanced function is a tool that performs complex calculations or operations on a field. Advanced functions accept an input and return an output.
You can apply advanced functions to fields independently, or in combination with aggregation functions. When applying an advanced function in combination with an aggregation function, you must first apply the aggregation function.
Example
Scenario
You are working with a table of risk data, and you need to include Risk IDs in your report. You tried adding the Risk Number field as a column in your report, but you are not seeing the appropriate values displayed.
In Projects, Risk IDs are stored as two separate fields:
- Objective Reference
- Risk Number
You need to concatenate the Objective Reference column with the Risk Number column so that your report contains a concatenated Risk ID value for each risk.
Process
To display the Risk ID value DA-001 in your report, you apply the Concatenate Columns advanced function to concatenate the Objective Reference column with the Risk Number column:
- Objective Reference DA
- Risk Number 001 001
Result
The new column Addition of column to Objective Reference is added to your report. The value in the column displays as DA001.
Permissions
Only Report Admins and Report Writers can apply advanced functions to report data.
Apply an advanced function to a field
- Open the Reports app.
The Browse page opens.
- Open a report and navigate to the Data tab in Report Builder.
- Locate the field you want to apply an advanced function to by doing any of the following:
- Enter a keyword in the Search field.
- Expand the folders to navigate to the appropriate field:
Dimension attributes of your data that describe records, such as Likelihood or Severity.
Metric values to be measured in the report, such as Cost Impact or Project Budget.
Date represent date values, such as Control Created or Control Updated.For more information, see Creating reports.
- Drag the field from the data fields list to the Rows, Columns, Filters or Sections list.
- Click the down arrow
beside the field name, and select Advanced Function. - Optional. To apply an advanced function in combination with an aggregation function, first define the aggregation function.
- Select the advanced function category from the Select Function dropdown list:
- Analysis analysis functions that can be applied to numeric fields
- Date & Time date and time analysis functions that can be applied to datetime fields
- Statistical statistical functions that can be applied to numeric and datetime fields
- Text allows you to concatenate character, numeric, or datetime fields
- Select an advanced function to apply from the list.
You can enter a search term to filter the list of advanced functions. For more information, see Available advanced functions.
- Define any additional parameters, as required.
Some advanced functions require you to specify additional parameters, such as a threshold percentage, a start date, or an additional column.
- Optional. Display only in charts enable this option to only apply the advanced function on the field in visualizations (not in tabular format). Tip
You can also apply an advanced function to a field in a chart, which can be different from the advanced function applied to the field in the table. For more information, see Visualizing report data using charts.
- To convert a field from one data type to another, click the Data Conversion tab, define the appropriate aggregation function to be applied to the field , click Add, select the appropriate data converter from the list, and click Save.
For more information, Available data converters.
- Click Save.
Result The advanced function is applied to the field independently, or in combination with an aggregation function.
TipYou can verify that advanced functions are displaying the appropriate results and make quick updates by navigating to the Design tab in Report Builder, clicking the down arrow
next to the column name, and selecting Advanced Function.
Available advanced functions
View a list of available advanced functions in Reports. The advanced functions available depend on the data type of the field the advanced function is being applied to.
Analysis
| Advanced function | Description |
|---|---|
| Accumulative Percentage |
returns values as a running percentage of the total |
| Accumulative Percentage with Cut-off |
displays a running percentage of total for the values in the field A maximum percentage cut-off can be specified, or set to user prompt |
| Accumulative Total | returns a running total for the values in the field |
| Ascending Rank |
returns values as rankings The highest value returned is given a rank of "1." |
| Bottom 10 Rank |
returns values as rankings The lowest value returned is given a rank of "1." This function returns only the 10 lowest rankings. |
| Bottom N Rank |
returns values as rankings The lowest value returned is given a rank of "1." This function returns only the specified number of lowest rankings. |
| Delta From Last | calculates the difference between the current value and the previous value (one row above) |
| Delta From Last N | calculates the difference between the current value and the value that is a specified number of rows above |
| Descending Rank |
returns values as rankings The lowest value returned is given a rank of "1." |
| Deviation from Previous |
displays the deviation from the previous value The formula used is: (Current Value - Previous Value) / Previous Value |
| Difference of Columns | subtracts one column from another |
| Division By Column | displays the result of the division of two selected columns, where the current column is the denominator and the numerator is defined in the setup |
| Division of Columns | displays the result of the division of two selected columns, where the current column is the numerator and the denominator is defined in the setup |
| Filter Percentage of Total |
displays the percentage of total for the values in the field, where the percentages are either above or below a specified threshold depending on the direction defined |
| Grouped Percentage of Total |
returns values as within or below a specified percentage threshold The threshold (Percentage Threshold) is specified by you. For example, a Percentage Threshold of 80% would return values as either 20% or 80% based on whether they are within the top 80% of the column total. |
| Multiplication of Columns | multiplies one column by another |
| Natural Logarithm | returns the base e logarithm of the values of a given field |
| Null to Zero | returns null values as zeros |
| Percentage Against Absolute Max |
Note This advanced function is currently not supported. |
| Percentage Against Column | returns values as percentages of another column |
| Percentage Against Maximum | returns the percentage of the attribute when compared to the maximum value of the attribute within the data set |
| Percentage Change Against Column | returns the percentage of the attribute when compared to the total summed value of the attribute for the entire data set |
| Percentage of Initial Value | returns values as a running percentage of the initial value in the column |
| Percentage of Total | returns values as percentages of the column total |
| Remove Values |
excludes values that are above / below a specified threshold |
| Sum of Columns | returns the sum of two selected columns |
| Top 10 Rank |
returns values as rankings The highest value returned is given a rank of "1." This function returns only the 10 highest rankings. |
| Top N Rank |
returns values as rankings The highest value returned is given a rank of "1." This function returns only the 10 highest rankings and returns only the specified number of highest rankings. |
| Top N With Ties |
returns value as Top N Rank, with provision for tied values Tied values are values with the same rank. This means that if there are multiple records per ranking, the results are restricted to n total rankings. |
| Top / Bottom N Percentage of Total | returns records that make up either the top or bottom n percentage of the total column |
| Truncate Data Set |
removes n rows from either the top or bottom of the data set |
Date & Time
The Reports server time is UTC and the Current Date field is displayed in UTC. UTC is a global time standard that has replaced Greenwich Mean Time (GMT). For most purposes, the two standards are equivalent. For UTC-based datetime data, if conversion to UTC goes forward or backward across the boundary of midnight, the date is adjusted by one day.
| Advanced function | Description |
|---|---|
| Days Between Date | returns the number of days between the date selected and another date column |
| Date Extrapolation |
extends the date range displayed in the table by a defined number of periods Both the number of periods, and the units can be defined. |
| Date Period Extractor |
returns a specific date component For example, you can extract a specific value, such as a year, quarter, or month, append a year value, or classify a date as a period by comparing it with the current date. |
| Days to Now | returns the number of days between the date selected and the current date (age in days) |
| Months Between Date | returns the number of months between the date selected and another date column |
| Months to Now | returns the number of months between the date selected and the current date (age in months) |
| Week Days Between | returns the number of week days between the date selected and another date column |
| Years Between Date | returns the number of years between the date selected and another date column |
| Years to Now | returns the number of years between the date selected and the current date (age in years) |
Statistical
| Advanced function | Description |
|---|---|
| Decile |
divides the values into 10 equal parts, and assigns a value of 1 to 10, based upon its rank to the highest value Deciles are used as a measure of dispersion. |
| Deviation | returns the number of deviations that the value is from the mean |
| Linear Regression |
shows data as increasing or decreasing at a steady rate |
| Mean |
returns the mean (sum of values / count). You can specify the mean to be grouped by a dimension field. For example, you may want to calculate the mean of Hours, grouped by Project Name. Instead of returning the mean for the entire column, the mean is calculated for each project, based on the values for each project. |
| Median | returns the value that separates the higher half of the values in the column from the lower half |
| Moving Average |
a moving average trendline smoothes out fluctuations in data to show a pattern or trend more clearly A moving average uses a specific number of data points (set by the Period option), averages them, and uses the average value as a point in the line. If Period is set to 2, for example, then the average of the first two data points is used as the first point in the moving average trendline. The average of the second and third data points is used as the second point in the trendline, and so on. |
| Moving Total | the total over the last n periods |
| Naive Forecasting |
a special case of the moving average forecasting model where the number of periods used for smoothing is 1 Therefore, the forecast for a period, t, is simply the observed value for the previous period, t-1. This function can only be used to forecast up to one period in the future. |
| Polynomial Regression |
a curved line that shows fluctuations in data This function is useful for analyzing gains and losses over a large data set. The order of the polynomial can be determined by the number of fluctuations in the data or by how many bends (hills and valleys) appear in the curve. An Order 2 polynomial trendline generally has only one hill or valley. Order 3 generally has one or two hills or valleys. Order 4 generally has up to three. |
| Quartile |
divides the values returned into four equal parts, and assigns a value of 1 to 4, based upon its rank to the highest value Quartiles are used as a measure of dispersion. |
| Standard Deviation | returns the standard deviation of the column |
| Standard Deviation From Mean | measures the dispersion of a set of data from its mean |
| Standard Score | calculates how many standard deviations each value is above or below the mean |
| Stepped Regression |
Note
This advanced function is currently not supported. |
| Trend | displays a trend metric against an extended period |
| Triple Exponential Smoothing | returns a triple exponential smoothing result based in the input data set |
| Variance | calculates the variance of the column |
| Weighted Moving Average | returns a moving average that is weighted so that the more recent the value, the more weight is applied to it |
Text
| Advanced function | Description |
|---|---|
| Concatenate Columns | joins the value with the value from another field |
Frequently used advanced functions
View a list of frequently used advanced functions and view examples of how you can apply these functions to Diligent One data.
Analysis
| Advanced function | Description | Example |
|---|---|---|
| Difference of Columns | subtracts one column from another |
Calculating the budget that remains for a project:
|
| Multiplication of Columns | multiplies one column by another |
Generating a risk score:
|
| Percentage of Total | returns values as percentages of the column total |
Calculating what percentage of each team member's time has been spent on a project:
|
| Percentage Against Column | returns values as percentages of another column |
Calculating what percentage of a project budget has been used:
|
Date & Time
| Advanced function | Description | Example |
|---|---|---|
| Days Between Date | returns the number of days between the date selected and another date column |
Calculating the number of days between issue creation and remediation:
|
| Days to Now | returns the number of days between the date selected and the current date (age in days) |
Calculating the number of days that an issue has been open:
|
| Week Days Between | returns the number of week days between the date selected and another date column |
Calculating the number of week days that an issue has been open:
|
Available data converters
Data converters manipulate data to transform fields from one data type to another. The converters available depend on the data type of the field and the aggregation function applied to the field.
Data converters only transform fields for display purposes. The transformed fields cannot be used in filters or calculated fields.
The following data converters are not supported:
- Filename BLOB
- Java Timestamp
- TimeZone
- WKT Geometry
| Converter | Converts... |
|---|---|
| Numeric Divide | numeric values by dividing them by a specified value |
| Numeric to Text | numeric fields into text fields |
| Text to Numeric | text fields that contain numeric values into numeric fields |
| Text to SQL Date | text values to SQL date values using a specified date format |
| Text to SQL Timestamp | text values to SQL timestamp values using a specified timestamp format |
