Compares two numeric values. You can use this function to compare metrics or attributes to other metrics, attributes, or constant values.
This function belongs to the Comparative category.
Syntax
LessThanEquals(Compare1, Compare2)
Return Value
Boolean. Returns TRUE if the first value is less than or equal to the second value. Returns FALSE if the first value is greater than the second value.
Examples
The following expression compares two metrics and returns TRUE if eastern region sales were greater than western region sales:
LessThanEquals({Eastern Sales}, {Western Sales}) Eastern Sales Western Sales Return Value
- - 200 220 TRUE 120 121 TRUE 122 122 TRUE 250 205 FALSE
Argument Datatype Description
Compare1 Varies Required. Value you want to compare. You can pass a numeric, date, or string value.
Compare2 Varies Required. Value you want to compare. You can pass a numeric, date, or string value.
Log 143
L OG
Returns the natural logarithm of a numeric value. The Log function uses E as the base for the logarithm calculation.
This function belongs to the Mathematical category.
Syntax
Log(NumericValue)
Return Value
Numeric
Examples
The following expression returns the natural logarithm of all values in the Number metric:
Log({Number}) Number Return Value --- 10 2.30 100 4.61 835.92 6.73 2 0.69
Argument Datatype Description
NumericValue Numeric Required. Numeric value you want to pass to the function.
L OG 10
Returns the base 10 logarithm of a numeric value.
This function belongs to the Mathematical category.
Syntax
Log10(NumericValue)
Return Value
Numeric
Examples
The following expression returns the base 10 logarithm of all values in the Number metric:
Log({Number}) Number Return Value --- 10 1.00 100 2.00 835.92 2.92 2 0.30
Argument Datatype Description
NumericValue Numeric Required. Numeric value you want to pass to the function.
Log2 145
L OG 2
Returns the base 2 logarithm of a numeric value.
This function belongs to the Mathematical category.
Syntax
Log2(NumericValue)
Return Value
Numeric
Examples
The following expression returns the base 2 logarithm of all values in the Number metric:
Log({Number}) Number Return Value --- 10 3.32 100 6.64 835.92 9.71 2 1.00
Argument Datatype Description
NumericValue Numeric Required. Numeric value you want to pass to the function.
L OWER
Converts uppercase string characters to lowercase.
This function belongs to the String category.
Syntax
Lower(String)
Return Value
String
Examples
The following expression returns all values in the Customer Name metric in lowercase:
Lower({Customer Name}) Customer Name Return Value - ruby ruby Rachel rachel MATTHEW matthew LeBron lebron
Argument Datatype Description
String String Required. String value you want to pass to the function.
Max 147
M AX
Returns the maximum value of a numeric metric or attribute.
This function belongs to the Aggregate category.
Syntax
Max(List [, Context])
Return Value
Numeric
Examples
The following expression returns 8.99 as maximum cost for products in your inventory:
Max({Cost}) Cost
5.96 1.56 8.99 6.56
Argument Datatype Description
List Numeric List Required. Name of a metric or attribute.
Context String Optional. Filter for setting a scope for the function. Context allows you to narrow the calculation to a set of attribute or metric values. You must enclose the Context within quotation marks. The syntax for the Context argument depends on the function where you are using it.
M AX 2
Returns the maximum of two numeric values.
This function belongs to the Mathematical category.
Syntax
Max2(NumericValue1, NumericValue2)
Return Value
Numeric
Examples
The following expression returns the maximum of the Domestic Sales and Overseas Sales metrics:
Max2({Domestic Sales}, {Overseas Sales}) Domestic Sales Overseas Sales Return Value -- -- 500865 200000 500865 8000 20000 20000 45000 1000 45000 1000 45000 45000
Argument Datatype Description
NumericValue1 Numeric Required. Numeric value you want to pass to the function. You can enter a numeric literal, numeric metric name, or numeric attribute name.
NumericValue2 Numeric Required. Numeric value you want to pass to the function. You can enter a numeric literal, numeric metric name, or numeric attribute name.
Median 149
M EDIAN
Returns the median value for a numeric metric or attribute. In a set of numeric data in ascending or descending order, the median is the value in the middle of the data set with an equal number of values distributed above and below that value. When there is an even number of values in the set, median is the average of the two numbers in the middle. Use the Context argument to narrow the scope of the function to specific metric or attribute values.
This function belongs to the Aggregate category.
Syntax
Median(List [, Context])
Return Value
Numeric
Examples
The following expression returns 5.5 as the median weight for all products:
Median({Weight})
This function returns the middle value when the data set has an odd number of values. It returns the average of the two middle values when the data set has even number of values.
This function ignores NULLs when performing the calculation.
Argument Datatype Description
List Numeric List Required. Name of a metric or attribute.
Context String Optional. Filter for setting a scope for the function. Context allows you to narrow the calculation to a set of attribute or metric values. You must enclose the Context within quotation marks. The syntax for the Context argument depends on the function where you are using it.
M IN
Returns the minimum value of a numeric metric or attribute.
This function belongs to the Aggregate category.
Syntax
Min(List [, Context])
Return Value
Numeric
Examples
The following expression returns 1.00 as the minimum profit margin among all products:
Min({Profit}) Profit
2.50 1.00 7.50 1.01
Argument Datatype Description
List Numeric List Required. Name of a metric or attribute.
Context String Optional. Filter for setting a scope for the function. Context allows you to narrow the calculation to a set of attribute or metric values. You must enclose the Context within quotation marks. The syntax for the Context argument depends on the function where you are using it.
Min2 151
M IN 2
Returns the minimum of two numeric values.
This function belongs to the Mathematical category.
Syntax
Min2(NumericValue1, NumericValue2)
Return Value
Numeric
Examples
The following expression returns the minimum of the Domestic Sales and Overseas Sales metrics:
Min2({Domestic Sales}, {Overseas Sales}) Domestic Sales Overseas Sales Return Value -- -- 500865 200000 200000 8000 20000 8000 45000 1000 1000 1000 45000 1000
Argument Datatype Description
NumericValue1 Numeric Required. Numeric value you want to pass to the function. You can enter a numeric literal, numeric metric name, or numeric attribute name.
NumericValue2 Numeric Required. Numeric value you want to pass to the function. You can enter a numeric literal, numeric metric name, or numeric attribute name.
M INUTE
Returns the minutes of the time part of a date value. The function returns the minutes as an integer with values ranging from 0 to 59.
This function belongs to the Date & Time category.
Syntax
Minute(Date)
Return Value
Numeric
Examples
The following expression returns the minutes of the hour of the Date Shipped attribute value:
Minute({Date Shipped}) Date Shipped Return Value ---- 2004-03-10 2:15 15 2004-06-06 14:53 53 2004-06-01 7:20 20 2003-12-15 0:37 37
Notes
To pass a date to this function, use a date attribute or another function that generates a date value. Examples of functions that generate a date value include Date, DateValue, Today, and AddToDate.
Argument Datatype Description
Date Date Required. Date value you want to pass to the function.
Mod 153
M OD
Returns the modulus, the remainder of a division calculation, for any numeric metric or attribute.
This function belongs to the Mathematical category.
Syntax
Mod(NumericValue1, NumericValue2)
Return Value
Numeric
Examples
Use the following expression to determine the remainders of sales divided by 10:
Mod({Sales}, 10) Sales Return Value --- 1500 0 7125 5 3500 0 1095 5
Argument Datatype Description
NumericValue1 Numeric Required. Numeric value you want to pass to the function. You can enter a numeric literal, numeric metric name, or numeric attribute name.
NumericValue2 Numeric Required. Numeric value you want to pass to the function. You can enter a numeric literal, numeric metric name, or numeric attribute name.
M ODE
Returns the value that occurs most frequently in a numeric metric or attribute. When more than one value occurs at the greatest frequency, then the Mode function returns the first value to reach the greatest frequency.
This function belongs to the Aggregate category.
Syntax
Mode(List [, Context])
Return Value
Numeric
Examples
The following expression returns 55000 as the most common salary:
Mode({Salary})
This function ignores NULLs when performing the calculation. If no value occurs more than once, PowerAnalyzer returns NULL.
Argument Datatype Description
List Numeric List Required. Name of a metric or attribute.
Context String Optional. Filter for setting a scope for the function. Context allows you to narrow the calculation to a set of attribute or metric values. You must enclose the Context within quotation marks. The syntax for the Context argument depends on the function where you are using it.
Month 155
M ONTH
Returns the month of a date value. The function returns the month as an integer, with values ranging from 1 (January) to 12 (December).
This function belongs to the Date & Time category.
Syntax
Month(Date)
Return Value
Numeric
Examples
All of the following examples return the number 9:
Month(Date(2004, 9, 10)) Month(DateValue("15-Sep-2004"))
Month(Today()), where today's date is September 10, 2004
Month(AddToDate(Today()), 0, 0, 9) where today's date is September 1, 2004 Month({Day Shipped}), where the value of the Day Shipped attribute is September 10, 2003
Notes
To pass a date to this function, use a date attribute or another function that generates a date value. Examples of functions that generate a date value include Date, DateValue, Today, and AddToDate.
Argument Datatype Description
Date Date Required. Date value you want to pass to the function.
M OVING A VG
Returns the average (row-by-row) of a specified set of values. The Size argument defines the row set, or window, for which you want to calculate the moving average. Use the Context argument to perform the calculation for the row, the column, or a time attribute used in the report.
This function belongs to the Running category.
Syntax
MovingAvg(List, Size [, Context])
Return Value
Numeric List
Examples
If you want to calculate the moving average for the Sales metric, five rows at a time, use the following expression. The function returns the average for a set of five rows: 358 based on rows one through five, 245.8 based on rows two through six, and 243 based on rows three through seven. For the first four rows, the function does not perform the moving average calculation and returns the same value as the metric value.
MovingAvg({Sales}, 5)
List Numeric List Required. Name of a metric or attribute.
Size Numeric Required. Number of elements for which you want to perform the calculation.
Context String Optional. Filter for setting a scope for the function. Context allows you to narrow the calculation to a set of attribute or metric values. You must enclose the Context within quotation marks. The syntax for the Context argument depends on the function where you are using it.
MovingAvg 157
Notes
This function ignores NULLs when performing the calculation.
M OVING S UM
Returns the cumulative sum (row-by-row) of a specified set of values. The Size argument defines the row set, or window, for which you want to calculate the moving sum. Use the Context argument to perform the calculation for the row, the column, or a time attribute used in the report.
This function belongs to the Running category.
Syntax
MovingSum(List, Size [, Context])
Return Value
Numeric List
Examples
If you want to calculate the moving average for the Sales metric, five rows at a time, use the following expression. The function returns the sum for a set of five rows: 1790 based on rows one through five, 1229 based on rows two through six, and 1215 based on rows three through seven. For the first four rows, the function does not perform the moving sum calculation and returns the same value as the metric value.
MovingSum({Sales}, 5)
List Numeric List Required. Name of a metric or attribute.
Size Numeric Required. Number of elements for which you want to perform the calculation.
Context String Optional. Filter for setting a scope for the function. Context allows you to narrow the calculation to a set of attribute or metric values. You must enclose the Context within quotation marks. The syntax for the Context argument depends on the function where you are using it.
MovingSum 159
Notes
This function ignores NULLs when performing the calculation.
M ULTIPLY
Returns the product of one numeric value multiplied by another.
This function belongs to the Mathematical category.
Syntax
Multiply(NumericValue1, NumericValue2)
Return Value
Numeric
Examples
The following expression multiplies the values of the Profit per Item and Quantity Sold metrics:
Multiply({Profit per Item}, {Quantity Sold}) Profit per Item Quantity Sold Return Value --- -
5 600 3000
2 504 1008
0 36 0
3 100 300
Argument Datatype Description
NumericValue1 Numeric Required. Numeric value you want to pass to the function. You can enter a numeric literal, numeric metric name, or numeric attribute name.
NumericValue2 Numeric Required. Numeric value you want to pass to the function. You can enter a numeric literal, numeric metric name, or numeric attribute name.
NPV 161
NPV
Calculates the net present value of an investment by using a discount rate and a series of cash flows. Use a metric to pass the cash flow values to the function. These cash flows do not have to be of equal value, but they must occur at regular periods of time, such as monthly or annually. In the cash flow, negative values represent future payments and positive values represent income. As the value of the Rate argument, pass the rate of discount over the length of one period. Use the Context argument to narrow the scope of the function to specific metric or attribute values.
This function belongs to the Financial category.
Syntax
NPV(CashFlows, Rate [, CurrencyDecimals, Context])
Return Value
Numeric
Examples
The following expression returns 31807.54 as the net present value of the asset after five months:
NPV({Cash Flows}, 0.12, "{Month}='January', 'February', 'March', 'April', 'May'")
CashFlows Numeric List Required. Name of a metric or attribute representing a series of cash flows.
Rate Numeric Required. Interest rate, expressed as a decimal number. Divide the percent rate by 100 to express it as a decimal number. Must be greater than or equal to 0.
CurrencyDecimals Numeric Optional. Currency places to the right of the decimal point. Default is 2.
Must be between 0 and 3, inclusive.
Context String Optional. Filter for setting a scope for the function. Context allows you to narrow the calculation to a set of attribute or metric values. You must enclose the Context within quotation marks. The syntax for the Context argument depends on the function where you are using it.
May 6974.26 June 89114.60 July 9174.55 August 6974.26 September 10502.07 October 6974.26 November 13253.74 December 7774.11
Notes
When you pass an optional argument, you must also pass all other optional arguments that occur before it in the function syntax.
NPer 163
NP ER
Returns the number of periods for an investment based on a constant interest rate and periodic, constant payments. The value of the Rate argument is the interest rate earned in each period. PresentValue is the lump-sum amount that a series of future payments is worth now. FutureValue is the cash balance you want to attain after the last payment is made. If you omit the FutureValue argument, the NPer function assumes it to be 0. For example, the future value of a loan is 0.
This function belongs to the Financial category.
Syntax
NPer(Rate, PresentValue, Payment [, FutureValue, PaymentAtBeginning])
Return Value
Numeric
Examples
The present value of an investment is $2,000. Each payment is $500 and the future value of the investment is $20,000. The following expression returns 9 as the number of periods for which you need to make the payments:
NPer(0.01, -2000, -500, 20000, TRUE)
Notes
To calculate interest rate earned in each period, divide the annual rate by the number of payments made in an year. For example, if you make monthly payments at an annual interest rate of 15 percent, the value of the Rate argument is 15% divided by 12. If you make annual payments, the value of the Rate argument is 15%.
The payment value and present value are negative because these are amounts that you pay.
Argument Datatype Description
Rate Numeric Required. Interest rate, expressed as a decimal number. Divide the percent rate by 100 to express it as a decimal number. Must be greater than or equal to 0.
PresentValue Numeric Required. Current value of an asset or liability.
Payment Numeric Required. Payment amount due. Must not equal 0.
FutureValue Numeric Optional. Worth of an asset in the future.
PaymentAtBeginning Boolean Optional. Timing of the payment. Enter TRUE if payment is at the beginning of period. Enter FALSE if payment is at the end of period.
Default is FALSE.
N EGATE
Adds the minus sign to a positive number or removes the minus sign from a negative number.
You can use the Negate function with a numeric metric, numeric attribute, or a calculated numeric value. You can also use this function within other functions, where you need to change the sign of a numeric value.
This function belongs to the Unary category.
Syntax
Negate(NumericValue)
Return Value
Numeric
Examples
The following expression returns -10:
Negate(10)
The following expression returns 10:
Negate(-10)
Argument Datatype Description
NumericValue Numeric Required. Numeric value you want to pass to the function.
Next 165
N EXT
Returns the next value for the specified metric.
This function belongs to the Fetch category.
Syntax
Next(Metric)
Return Value
Varies
Examples
The following expression returns the revenue from the next row:
Next({Revenue}) Revenue Return Value --- 1500 7125 7125 3500 3500 1090 1090 NULL
Notes
The function returns NULL if the specified value is the last value in the metric.
Argument Datatype Description
Metric Varies Required. Metric for which you want to get the value.