Advanced Functions by Category

There are three major categories of functions: Analysis, Statistical, and Text.

ANALYSIS Function

Description

Accumulative Percentage

Generates a running percentage for the values returned. A maximum of 100% will be displayed.

Accumulative Total

Generates a running total for the data returned.

Ascending Rank

Returns the highest value, displayed as a 1. Use when the preferable result is a higher value (e.g., profit).

Bottom 10 Rank

Returns the bottom 10 values (lowest).

Bottom N Rank

Returns the bottom N values (lowest), and the user is prompted to define number of values to return.

Delta from Last

Calculates the change between consecutive rows.

Delta from Last N

Calculate the change between the current row and row N.

Descending Rank

Returns the lowest value, displayed as a 1. Use when the preferable result is a lower value (e.g., expenses).

Difference of Columns

Returns the difference of two selected columns.

Multiplication of Columns

Returns the multiplication of two selected columns.

Natural Logarithm

Gives the base e logarithm of the values of a given field

Percentage Against Absolute Maximum

Returns the percentage of a selected field according to an absolute maximum value.

Percentage Against Column

Creates a percentage ration of values in a selected column compared to another column.

Percentage Against Maximum

Returns the percentage of the attribute when compared to the maximum value of the attribute within the dataset.

Percentage of Total

Returns the percentage of the attribute when compared to the total summed value of the attribute for the entire dataset.

Sum of Columns

Returns the sum of two selected columns.

Top 10 Rank

Returns the top 10 values (highest).

Top N Rank

Returns the top 10 values (highest), and the user is prompted to define number of values to return.

Top N With Ties

Returns top values for the selected field with a provision for tied values. If there are multiple records per ranking, the calculation will be restricted to N total rankings.

STATISTICAL Function

Description

Decile

Divides the rows returned 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

Number of deviations from the mean.

Linear Regression

A linear trend line is a best-fit straight line that is used with simple linear data sets. Data is linear if the pattern in its data points resembles a line. A linear trend line usually shows that something is increasing or decreasing at a steady rate.

Mean

Sum of all the members in a list of numbers divided by the number of items in the list.

Median

Number separating the higher half of a sample, a population, or a probability distribution, from the lower half.

Mode

Value that occurs the most frequently in a data set.

Moving Average

A moving average trend line smooths 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. Example: If Period is set to 2, then the average of the first two data points is used as the first point in the moving average trend line. The average of the second and third data points is used as the second point in the trend line, and so on.

Moving Total

Total over the last N periods.

Naïve Forecasting

A naive forecasting model is 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. Due to the simplistic nature of the naive forecasting model, it can only be used to forecast up to one period in the future. It is not at all useful as a medium-long range forecasting tool.

Polynomial Regression

A polynomial trend line is a curved line that is used when data fluctuates. Example: It 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 trend line generally has only one hill or valley, an Order 3 generally has one or two hills or valleys, and an Order 4 generally has up to three.

Quartile

Divides the rows returned into 4 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

Measure of the dispersion of a set of values. A standard deviation can apply to a probability distribution, a random variable, a population or a multiset.

Standard Score

Indicates how many standard deviations an observation is above or below the mean. It allows comparison of observations from different normal distributions, which is done frequently in research.

Variance

Returns the difference between the data sets.

Weighted Moving Average

Returns a moving average that is weighted so that the more recent the value, the more weight is applied to it.