List of formulas

Contents[Hide]

1. Overview

Dundas BI lets you apply mathematical formulas to data by entering small script-like expressions in a toolbar, similar to a desktop spreadsheet program. These formula expressions typically include mathematical function calls such as AVG and SUM.

This article lists the built-in math functions that are available for use in formula expressions.

Entering a formula in the Formula toolbar
Entering a formula in the Formula toolbar

Related video: Working with Formulas

  

Add a formula caption and unique name (optional):

Click the Advanced button
Click the Advanced button

Enter the caption and unique name
Enter the caption and unique name

For an existing formula measure, you can access the formula bar directly from the data analysis panel:

Shortcut to the formla bar
Shortcut to the formla bar

2. Standard functions

General purpose functions for performing standard math operations such as calculating the mean or generating a moving average.

FunctionNameDescription
AVG Mean (Simple Average) The Mean function computes the mean (or simple average) of a set of input values.
BOLLINGERUPPER BOLLINGERLOWER Bollinger Bands The Bollinger Bands function was developed by John Bollinger. It computes a pair of data bands that envelops a simple moving average of the input value series. Each data band is positioned a specified number of standard deviations away from the simple moving average line. Applications of Bollinger Bands include the measurement of stock price volatility.
CMLTOTAL Cumulative Total The Cumulative Total function computes a running sum or total of a set of input values.
EXPMOVAVG Exponential Moving Average The Exponential Moving Average function computes the average of a set of input values over a specified number of time periods. In this function, a greater weight is given to more recent data. This function can be used to smooth a data series, which helps to reduce noise and make it easier to spot data trends.
HISTVOL Historical Volatility The Historical Volatility function calculates the volatility of a set of input data values, such as stock prices over a period of time.
MEDIAN Median The Median function computes the median (e.g. middle) value from a set of input values.
MODE Mode The Mode function finds the value that is repeated more often than any other in a value series.
MOVAVG Moving Average The Moving Average function computes the average of a set of input values over a specified number of time periods. The smaller the number of time periods, the faster the SMA responds to changes in the input values.
MOVAVGEVPUPPER MOVAVGEVPLOWER Moving Average Envelopes The Moving Average Envelopes function computes a pair of data bands that envelops a moving average of the input data values. Each data band is positioned a percentage-based magnitude away from the moving average line. The average line can be calculated using a simple moving average or an exponential moving average. Applications of the Moving Average Envelopes function include indication of overbought/oversold conditions and price trends for a security.
PERCENTTOTAL Percent of Total The Percent of Total function returns the percentage of each value in the selected series out of the total sum.
SUM Totals The Totals function calculates the total sum for any number of input value series.
TRENDFORE TRENDFOREUPPER TRENDFORELOWER TRENDFOREAXIS Trend and Forecasting The Trend and Forecasting function applies a regression function to historical data in order to forecast future values based on the best fit.
WGHTMOVAVG Weighted Moving Average The Weighted Moving Average function computes the average of a set of input values over a specified number of time periods. In this function, a greater weight is given to more recent data. The function can be used to smooth a data series, which helps to reduce noise and make it easier to spot data trends.

 

3. Statistical functions

Use statistical functions to perform statistical analysis on your data.

FunctionNameDescription
CORRELATION Correlation The Correlation function calculates the correlation between two input data series. It measures the strength of the relationship between the two series. The resulting correlation value ranges between -1 and 1. A correlation value of zero implies that the two series are independent and have no relationship. A correlation value that is close to 1 implies that the two series are positively correlated. Similarly, a correlation value that is close to -1 implies that the two series are negatively correlated. If the correlation value is negative, this means that when the first input is large, the second input is small (and vice-versa).

HISTOGRAM HISTOGRAMRANGE

Histogram The Histogram function assigns numeric values to bins and returns the counts.
NORMALDIST Normal Distribution The Normal Distribution function calculates the standard normal cumulative distribution for an input data series. Specifically, the function computes the probability that a series, which has a mean of 0 and a standard deviation of 1, contains a value less than the input value.
PERCENTILE Percentile The Percentile function returns a scalar value below which the specified percentage of input data values fall.
PERCENTRANK Percent Rank The Percent Rank function calculates the percent rank for each value in a set of input values. Specifically, the function computes the percentage of all input values that are less than a given input value. Percent Rank values range between 0 and 1.
STDEV Sample Standard Deviation The Sample Standard Deviation function measures volatility as the difference between values and their moving average. A larger difference implies a larger standard deviation value.
STDEVPOP Population Standard Deviation The Population Standard Deviation function measures volatility as the difference between values and their average. A larger difference implies a larger standard deviation value.
VARIANCE Variance The Variance function calculates the variance of a set of input values. It measures how spread out a set of values are from each other. Variance is defined as the average of the squared differences of each value from the mean of the input values.

4. Subset functions

Subset functions let you return a subset of your input data based on various criteria.

FunctionNameDescription
BOTTOM Minimum Subset The Minimum Subset function returns a specified number of the smallest values in an input data series. For example, the function can be used to return the 3 smallest values.
FIRST First Subset The First Subset function returns a specified number of values that appear first in an input data series. For example, the function can be used to return the first 3 values.
LAST Last Subset The Last Subset function returns a specified number of values which appear last in an input data series. For example, the function can be used to return the last 3 values.
MIDDLE Middle Subset The Middle Subset function returns a specified number of the middle values in an input data series. For example, the function can be used to return the 3 middle values in a set of input values.
TOP Maximum Subset The Maximum Subset function returns a specified number of the largest values in an input data series. For example, the function can be used to return the 3 largest values.
ZIGZAG Zig Zag Subset The Zig Zag Subset function returns the input data values that show movement greater than the specified sensitivity percentage value.

5. Financial functions

Financial functions are used in financial applications such as technical analysis. Generally, these functions accept the price and/or volume of a security (e.g. a stock) as input and generate a result set that may help you to confirm price trends, spot reversals, or determine when to buy or sell.

FunctionNameDescription
ACCDIST Accumulation Distribution The Accumulation Distribution function is a technical indicator that considers both stock price and volume. When the indicator increases, it implies an accumulation or buying of the related stock. When the indicator decreases, it implies distribution or selling of the related stock. Accumulation Distribution can be used to detect divergences between stock price movement and volume movement. Other applications include confirmation of price trends and providing advance warning of a possible reversal in stock price.
AROONOSC AROONOSCUP AROONOSCDOWN Aroon Oscillator The Aroon Oscillator function is calculated as the difference between the Aroon Up and Aroon Down indicators, with resulting values ranging from -100 to 100. An uptrend in stock price is indicated when the Aroon Oscillator value is positive. A downtrend is indicated when the oscillator value is negative. The further away the Aroon Oscillator value is from the zero line, the stronger the trend. Applications of the Aroon Oscillator include determining whether a stock is trending or not, and gauging the strength of the current trend.
AVGDIRECT AVGDIRECTPOS AVGDIRECTNEG Average Directional Index The Average Directional Index function was developed by J. Welles Wilder. It combines two other indicators, the Positive Directional Indicator and the Negative Directional Indicator, which were also developed by Wilder. Applications include indicating the strength of the current trend in stock price (but not the trend direction).
AVGTRURANGE Average True Range The Average True Range function computes an average of the True Range, which represents the range in price of a stock or commodity over the course of one day, or from one day to the next. Average True Range is used to indicate price volatility.
CHKOSC Chaikin Oscillator The Chaikin Oscillator function was developed by Marc Chaikin. It is calculated by subtracting the 10-day exponential moving average from the 3-day exponential moving average of the Accumulation Distribution line. Resulting values oscillate around the zero level. Applications of Chaikin Oscillator include confirmation of price trends and providing advance warning of price reversals.
CHKMONEYFLO Chaikin Money Flow The Chaikin Money Flow function was developed by Marc Chaikin. It computes the sum of the Accumulation Distribution line values for a stock over a specified number of time periods, and divides this by the total stock volume over the same duration. When the CMF value is above zero, this indicates buying pressure. When the CMF value is below zero, this indicates selling pressure. Applications of Chaikin Money Flow include determining if a stock is under accumulation or distribution. For example, if the CMF value remains above zero for an extended period of time, this indicates accumulation.
COMMCHANNEL Commodity Channel Index The Commodity Channel Index function was developed by Donald Lambert. It compares a stock price to its average over a specified number of time periods. Applications of CCI include identification of overbought or oversold conditions, and signaling when a price trend may be weakening.
FASTSTOC FASTSTOCTRG Fast Stochastic The Fast Stochastic function compares the close price of a stock against its price range (high-low) over a specified number of time periods. Applications of this function include the generation of buy and sell signals.
MOMENTUM Momentum Momentum is a trend-following indicator that measures the speed of price movements by comparing the current stock price against its value from a specified number of time periods in the past. A stock price has upward momentum when the indicator value is positive, and downward momentum when the indicator value is negative. Applications of Momentum include indication of possible trend reversals.
MONEYFLO Money Flow Index The Money Flow Index function computes a relationship between the typical stock price and stock volume over a specified number of time periods. Resulting values range between 0 and 100. MFI is similar to the Relative Strength Indicator except that it incorporates volume. Applications of Money Flow Index include identifying market tops and market bottoms, and indicating price reversals.
MOVAVGCD MOVAVGCDSIG MOVAVGCDHIST Moving Average Convergence-Divergence The Moving Average Convergence-Divergence function was developed by Gerald Appel. It computes the difference between a short-term and a long-term exponential moving average of the stock price. Applications of MACD include generation of buy and sell signals. Signals are generated when the MACD line crosses the zero line or its signal line.
NEGVOLUME Negative Volume Index The Negative Volume Index function calculates a cumulative value based on days where the trading volume has decreased from the previous day. On days where the volume remained the same or increased, the NVI remains unchanged. Applications of NVI include indication of market trends. For example, a bull market may be indicated when the NVI rises above its one-year moving average.
ONBALVOLUME On Balance Volume The On Balance Volume function was developed by Joseph Granville. It calculates a cumulative volume that depends on stock price. When the closing stock price for the day is greater than the previous day's close, the volume for the day is added to the cumulative result. If the stock price decreases, volume is subtracted from the result. Applications of On Balance Volume include confirmation of price movements.
ONBALVOLWGHT On Balance Weighted Volume The On Balance Weighted Volume function calculates a cumulative volume that depends on stock price. The daily volume is weighted by the difference in stock price between the day's close and the previous day's close before it is added to the cumulative result. Applications of On Balance Weighted Volume include confirmation of price movements and providing indication of possible trend reversals.
PARABOLSAR Parabolic SAR The Parabolic SAR (stop-and-reverse) function was developed by J. Welles Wilder, Jr. It is a lagging technical indicator that can help you to determine when a stock is set to have a change in trend. Applications of Parabolic SAR include generation of buy-sell signals and, in particular, determining when to exit from trades.
POSVOLUME Positive Volume Index The Positive Volume Index function calculates a cumulative value based on days where the trading volume has increased from the previous day. On days where the volume remained the same or decreased, the PVI remains unchanged. Applications of PVI include indication of market trends. For example, a bear market may be indicated when the PVI drops below its one-year moving average.
RATECHANGE Rate of Change The Rate of Change function is a variation on the Momentum function. ROC calculates the percentage difference between the day's closing stock price and the price from a specified number of time periods in the past. Applications of ROC include indication of price reversals. For example, if the price is trending upward while the ROC is moving downward, this may indicate a large drop in price is forthcoming.
RELSTRENGTH Relative Strength Indicator The Relative Strength Indicator function was developed by J. Welles Wilder. It compares the average of up closes against the average of down closes over a specified number of time periods. Resulting values range from 0 to 100. Applications of RSI include identifying overbought or oversold situations. For example, if the RSI line stalls at a level above 70, this may indicate the stock is overbought. Or, if the RSI line drops to a level below 30, this may indicate the stock is oversold and becoming undervalued.
SLOSTOC SLOSTOCTRG Slow Stochastic The Slow Stochastic function compares the close price of a stock against its price range (high-low) over a specified number of time periods. It is derived by applying a 3-period, simple moving average to the Fast Stochastic line. Applications of Slow Stochastic include the generation of buy and sell signals.
STOCOSC STOCOSCTRG Stochastic Oscillator The Stochastic Oscillator function compares the close price of a stock against its price range (high-low) over a specified number of time periods. Applications of Stochastic Oscillator include the generation of buy and sell signals.
TRENDCONF Trend Confirmation Indicator The Trend Confirmation Indicator function computes the ratio between a short-term moving average and a longer-term moving average of the stock price. Resulting values oscillate around the 100 level. Applications of TCI include generation and confirmation of buy and sell signals. For example, when the TCI advances above 100, this may be interpreted as a buy-signal.
ULTIMOSC Ultimate Oscillator Oscillator The Ultimate Oscillator function was developed by Larry Williams. It computes a weighted total of three oscillators, each of which is calculated using a different time period. Resulting values range from 0 to 100. Applications of Ultimate Oscillator include buy and sell signals, and providing indication of overbought or oversold conditions.
VOLUMEOSC VOLUMEOSCSIG VOLUMEOSCHIST Volume Oscillator The Volume Oscillator function computes the relationship between a short-term moving average and a long-term moving average of the stock volume. Resulting values oscillate around the zero line. Applications of Volume Oscillator include indication of volume surges, which may be due to panic selling or greedy buying.
WILLIAMSR Williams %R The Williams %R (or Percent Range) function was developed by Larry Williams. It is similar to the Stochastic Oscillator function in that it relates the close price of a stock to its price range (high-low) over a specified number of time periods. Applications of Williams %R include indication of price reversals and determining overbought or oversold conditions.

6. See also

Dundas Data Visualization, Inc.
500-250 Ferrand Drive
Toronto, ON, Canada
M3C 3G8

North America: 1.800.463.1492
International: 1.416.467.5100

Dundas Support Hours: 7am-6pm, ET, Mon-Fri