List of formulas

Contents[Hide]

1. Overview

Dundas BI lets you apply mathematical formulas to data by entering small expressions into the formula bar, similar to a spreadsheet program. These formula expressions typically include functions such as AVG and SUM, arithmetic operators or other math functions, or a combination of these.

Entering a formula in the Formula bar
Entering a formula in the Formula bar

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

Related video: Formulas

2. Standard functions

These are the most common functions for statistical or financial calculations, such as calculating the mean or generating a moving average. Additional statistical and financial functions can be found in subsequent sections.

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.
CLUSTERING K-Means Clustering

The Clustering function uses the K-Means algorithm to split data points into clusters based on similarity of the measures provided.

DISTANCE Distance

The Distance function computes the as-the-crow-flies distance between a source and destination identified by latitude and longitude.

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.
RANKVAL Rank Value

The Rank Value function returns the value found at the specified position, either starting from the highest or the lowest value.

SUM Totals The Totals function calculates the total sum for any number of input value series.
TRENDFORE TRENDFOREUPPER TRENDFORELOWER  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).

CORRMATRIX
CORRMATRIXCOLUMN
CORRMATRIXROW

Correlation Matrix The Correlation Matrix calculates the strength of the relationships between all possible pairings of the specified data series as a value between -1 and 1. Zero implies that the two series are independent; a value close to 1 implies they are positively correlated, and a value close to -1 implies they are negatively correlated. A negative correlation means that when the first input is large, the second input is small (and vice-versa).

FORECASTING
EXPSMOOTH

Exponential Smoothing The Exponential Smoothing functions apply an exponentially-decreasing weight to historical data in order to forecast future values based on emerging trends.

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. Math functions

In addition to calling the functions listed above, you can use arithmetic operators and the following math functions. (These are .NET Math fields and methods).

While Dundas BI's formula functions listed above work with the entire series of input values you pass to them, arithmetic operators and math functions operate on each row's values separately. For example, $Measure 1$ / $Measure 2$ divides each row's Measure 1 value by its Measure 2 value, while SUM($Measure 1$) takes an entire set of measure values and outputs a single sum. The math function call Math.Round($Measure 1$) takes each row's measure value and rounds it to the nearest whole number.


Note
Additional operators and other script features such as if statements can be used in advanced formulas. For more details, see Adding Formulas.

OperatorDescription
+ Add
- Subtract
* Multiply
/ Divide
() Specify order of operations
% Calculate the remainder after dividing

 

FieldDescription
Math.E Represents the natural logarithmic base, specified by the constant, e.
Math.PI Represents the ratio of the circumference of a circle to its diameter, specified by the constant, π.

 

MethodDescription
Math.Abs Returns the absolute value of a decimal or a number.
Math.Acos Returns the angle whose cosine is the specified number.
Math.Asin Returns the angle whose sine is the specified number.
Math.Atan2 Returns the angle whose tangent is the quotient of two specified numbers.
Math.Ceiling Returns the smallest integral value that is greater than or equal to the specified number.
Math.Cos Returns the cosine of the specified angle.
Math.Cosh Returns the hyperbolic cosine of the specified angle.
Math.Exp Returns e raised to the specified power.
Math.Floor Returns the largest integer less than or equal to the specified number.
Math.Log Returns the natural logarithm of the specified number.
Math.Log10 Returns the logarithm of the specified number.
Math.Max Returns the larger of two numbers.
Math.Min Returns the smaller of two numbers.
Math.Pow Returns the specified number raised to the specified power.
Math.Round Rounds a decimal or double-precision value to the nearest integer.
Math.Sign Returns an integer that indicates the sign of a number.
Math.Sin Returns the sine of the specified angle.
Math.Sinh Returns the hyperbolic sine of the specified angle.
Math.Sqrt Returns the square root of the specified number.
Math.Tan

Returns the tangent of the specified angle.

Math.Tanh Returns the hyperbolic tangent of the specified angle.
Math.Truncate Returns the integral part of the specified number.

7. 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