Performing simple mathematical calculations

Follow

You can use simple numeric functions to perform basic mathematical calculations. For more advanced calculations, see Using advanced numeric functions.

Available numeric functions

This section lists all available simple numeric functions. In the Functions window, you can view only numeric functions by clicking the Filter button, then selecting the Numeric category.

Table 1. Numeric functions
Function Definition
GROWTH_RATE(Number, Number) Calculates change from the first entered number (x) to the second entered number(y), such as (y-x)/x.
POWER(Number, Number) Computes and returns the first entered number to the power of the second entered number.
MAXIMUM(Number, Number) Evaluates two parameters and returns the largest value. Remember you can find the maximum value within a given metric by choosing the MAX data aggregator.
MINIMUM(Number, Number) Evaluates two parameters and returns the smallest value. Remember, you can find the minimum value within a given metric by choosing the MIN data aggregator.
NUMBER(param) Returns the number representation of the given parameter, or returns NaN when it cannot be displayed as a number. When the given parameter is a date, it returns the number of milliseconds between 0:00:00 GMT January 1, 1970 and the given date.
INTEGER(param) Returns the integer representation of the given parameter.
ABS(Number) Returns the absolute value of the given parameter (ignores any number sign).
SIGN(Number) Returns -1 if the number is negative, 0 if zero, and 1 if positive.
ROUND(Number) Rounds the value of the number entered in the parameter up or down to the nearest integer. If equidistant, the value is rounded up.
FLOOR(Number) Returns the closest integer less than or equal to the number entered in the parameter.
CEIL(Number) Returns the closest integer greater than or equal to the number entered in the parameter.
PI() Returns the mathematical constant for the ratio of the circumference of a circle to its diameter, expressed as PI. The value is 3.141592653589793.
EXP(Number) Returns the value of the base of the natural logarithm (e), to the power of the exponent entered in the parameter.
SQUARE(Number) Returns the square of the entered number.
SQRT(Number) Returns the square root of the entered number.
RANDOM() Returns a pseudo-random number 'n' where 0<=n<1. If you need a random integer, use the formula Round(Random()*100).
IS_NAN(value) Returns true if the entered value is NaN(not a number).
LN(number) Returns the natural logarithm of the entered expression or number
LOG2(Number) Returns the Base-2 logarithm of the entered number or expression.
LOG10(Number) Returns the Base-10 logarithm of the entered number or expression
LOG(Base, Number) Returns the logarithm of entered number to entered base.
CURRENCY_CONVERT(value, from_currency,to_currency) Converts the entered value from the entered from_currency to the entered to_currency. You can add a date as the last parameter to get the currency exchange on that date. Currencies are represented by their ISO code.
EXCHANGE_RATE(from_currency, to_currency_ Returns the exchange rate between the entered from_currency and the entered to_currency. You can add a date as the last parameter to get the exchange rate on that date. Currencies are represented by their ISO code.
FAHRENHEIT(temperature) Convert a temperature in Celsius to a temperature in Fahrenheit.
CELSIUS(temperature) Convert a temperature in Fahrenheit to a temperature in Celsius.

Writing formulas with mathematical operators

In addition to simple numeric functions, you can use basic mathematical operators to perform calculations and connect text. The following operators are available in BIME:

  • +: Addition, or used to concatenate text values (see Using text functions).
  • -: Subtraction
  • *: Multiplication
  • /: Division
  • %: Modulo/remainder

The modulo operator is very useful for converting elements. For example, you can use the modulo in Google Analytics to convert time in seconds into session timings.

The example below uses mathematical operators to calculate the total revenue, but you can use these operators in other formulas.

To calculate total revenue

  1. In the Calculation engine, select Standard calculated metric.
  2. Name your calculated metric.
  3. Select your price metric from the Select a field drop-down list or type in the metric name.
    Note: If you type in your metric, you must either select BIME's auto-complete suggestion or replicate the metric in the same formatting. Calculations will not process incorrect formatting.
  4. Type an asterisk (*).
  5. Select your quantity metric from the Select a field drop-down list or type in the metric name.

  6. Click Save.
  7. Click the + button on metrics to add your calculated metric to your query.
Have more questions? Submit a request

6 Comments

  • 1

    How about a simple example of a formula?   

    =sum(price)*sum(sold)  doesn't work.

    sum(price)*sum(sold) doesn't work.

    (sum(price)*sum(sold)) doesn't work.

    What is the correct format?

  • 0

    Great idea, Jim. This would be very useful.

    The correct format would look something along the lines of SUM(price)*SUM(sold) and this would need to be created as a Calculated Measure. You don't need to include the equals sign. 

    Here is an example of how to create a gross profit calculation:

     

  • 0

    Hi,

     

    I tried to write 2 simple formula: 

    1) Sum (HR Screening) / Sum (New Cvs), that I want in % 

    It was approved with the green tick symbol, but did not give any results. 

    2) How can I formulate the  % per sales person over the total revenues?

    Example: Salesman X = 100$ our of 1,000 of Total revenues = 10%   

     

    Any idea? 

    Thanks and I am looking forward to reading you soon,

    Best 

    Edited by william ver
  • 0

    Hi William,

    This can be done a couple of ways. First, you could multiply your calculation by 100 or you can adjust the display format options.

  • 0

    Hi,

    I am trying to do the following math, but it's giving an error! 

    SUM(metric1 * metric 2) / SUM(metric1) 

    Can you help me out in this? What would be the proper way?

  • 0

    Hi Pushkar,

    You're almost there! 

    You'd need to wrap both metrics 1 & 2 with an aggregator. So in this case, it'd be:

     

    (SUM(metric1)*SUM(metric2))/SUM(metric1)

Please sign in to leave a comment.
Powered by Zendesk