Try using this table if you know what you want to do but don't know which function to use.
| Behavior | Description | Function | |
| Add |  |
Add a specified number of months to a date. Date format depends on your locale. | ADDMONTHS |
 |
Add a specified number of years to a date. Date format depends on your locale. | ADDYEARS | |
| Aggregate |  |
Aggregate the original line item values from the source module or modules that you selected for your line item subset. Collects the data from the original line items from one or more modules (module must have a line item subset as a dimension). |
COLLECT |
 |
Aggregate between the From and To time periods to sum or average over selected periods. These can be absolute time periods or relative to the latest actual time period. | TIMESUM | |
 |
Aggregate Boolean line items on source and result. Returns Boolean result TRUE only when the source Boolean is TRUE for all items of a list formatted source line item. | x[ALL:y] | |
 |
Aggregation function that can be used with Boolean line items on source and result. Returns Boolean result TRUE when the source Boolean is TRUE for any items of a list formatted source line item. | x[ANY:y] | |
| Calculate |  |
Calculate the number of agents required to service a given number of calls and meet the Service Level Agreement. | AGENTS |
 |
Calculate the number of agents required to handle the busy-period call traffic, given a percentage of calls that might receive a busy tone. Can be used to calculate the number of agents required to service a given number of calls and meet the service level agreement. | AGENTSB | |
 |
Calculate the minimum waiting time for which the service level agreement can be maintained. | ANSWERTIME | |
 |
Calculate the arrival rate of calls that can be received while guaranteeing the Service Level Agreement. | ARRIVALRATE | |
 |
Calculate the average duration of calls that are answered while guaranteeing the Service Level Agreement. | AVGDURATION | |
 |
Calculate the average waiting time for incoming calls. | AVGWAIT | |
 |
Calculate the difference in a value in the Current Period vs Previous Period. | DECUMULATE | |
 |
Calculate the probability a call will be blocked entirely, assumes there are no calls on queue. | ERLANG-B | |
|
Calculate the probability a call will be placed in an unlimited queue. | ERLANG-C | |
 |
Calculate the future residual value of an investment. This is the lump sum or closing balance you will receive back at the end of the investment. | FV | |
 |
Calculate the internal rate of return of a series of future cash-flows. | IRR | |
 |
Calculate moving values, such as a moving sum or moving average. | MOVINGSUM | |
 |
Calculate the number of periodic payments that are needed given the interest rate, opening and closing balances and the payment values required. | NPER | |
 |
Calculate the net present value of a series of cash-flows using a constant interest rate. | NPV | |
 |
Calculate the payments for a loan or annuity with constant payments and a constant interest rate. | PMT | |
 |
Calculate the present value of future cash-flows. This calculates the required opening balance for an account given the target closing balance, the interest rate, periodic payments required and the number of periods. | PV | |
 |
Calculate the interest rate per period. If the timescale is monthly, then the monthly rate is calculated. | RATE | |
 |
Calculate the percentage of incoming calls to be answered within a target answer time with a Service Level Agreement. | SLA | |
 |
Calculate the square root of a value. | SQRT | |
 |
Calculate the average for a specified range of values. | x[AVERAGE:y] | |
| Compare |  |
Compare two text formatted expressions to return a numeric result: Result is:
There are optional parameters available, which allow you to refine the comparison: Mode parameters you can use to control the strength of the comparison. |
COMPARE |
| Compose |  |
Compose the To, cc, Bcc, Subject and Body of an email. | MAILTO |
 |
Compose a click-able URL. | MAKELINK | |
| Convert |  |
Convert numbers yyyy, mm, and dd to a date. Date format depends on your locale. | DATE |
 |
Convert a date to a day in number format. Date format depends on your locale. | DAY | |
 |
Convert text source x to all lowercase. | LOWER | |
 |
Convert a date or a time period to a month in number format. | MONTH | |
 |
Convert a date parameter to a time period as result. The time period returned for the date parameter is determined by the time period granularity of the time period formatted result line item. | PERIOD | |
 |
Convert a numeric value to text - used to convert content from a number formatted cell for use in a text field. | TEXT | |
 |
Convert text to uppercase. | UPPER | |
 |
Convert a numeric value in a text field to a number format. | VALUE | |
 |
Convert a date or a time period to a year in number format. | YEAR | |
| Cumulate |  |
Cumulate the sum from the first period of the timescale. Take a numeric line item as source and returns cumulative sum either from the first time-period across the entire timescale, or across the items of a named list. | CUMULATE |
 |
Cumulate line item values based on ranking criteria, which might contain groupings. Can be used against list but not time. | RANKCUMULATE | |
 |
Cumulate the sum of a single numeric parameter across a half-year time range and then reset. | HALFYEARTODATE | |
| Divide |  |
Divide x/y except that 1/0=Infinity and 0/0=NaN (not a number). | DIVIDE |
| Extract |  |
Extract a substring from a string starting at the leftmost character. | LEFT |
|
Extract a substring from a string starting at any character. | MID | |
|
Extract a substring from a string starting at the rightmost character. | RIGHT | |
| Find |
|
Find a text string within a text formatted item, starting at a specified character number (result is a number that denotes the character position). | FIND |
 |
Find a valid item in a list, or list of time periods, by matching a text string or code to the list line items. Take a list as first parameter, and name or code text string as second parameter, and matches the text string to list item line item. You can use FINDITEM to search a list and return a matched list line item, if it belongs to the list. | FINDITEM | |
| List |  |
List a series text strings together in a single cell. | x[TEXTLIST:y] |
| Lookup |  |
Return the value at the intersection of one or more mappings from a source module. Each mapping matches a list formatted line item, a time period formatted item, or property from the source with a dimension of the target line item. | LOOKUP |
| Merge |  |
Merge a series of text cells into a single text cell, separated by a comma or other separator. | TEXTLIST |
| Offset |  |
Return the value from a specified line item a number of periods earlier than the selected cell. The MODE argument controls whether n can be a non-positive value. | LAG |
| Raise |  |
Raise a value to a power. | POWER |
| Rank |  |
Rank a set of values from 1 to a specified ending rank. | RANK |
| Return |  |
Return the absolute value of a number. | ABS |
 |
Return the code of a list item. | CODE() | |
 |
Return the END date of the Current Period of the Time Scale on the Settings tab | CURRENTPERIODEND | |
 |
Return the START date of the Current Period of the Time Scale on the Settings tab. | CURRENTPERIODSTART | |
 |
Return the value of the current version as defined under Versions on the Settings tab. | CURRENTVERSION | |
 |
Return the number of days in a time period: Takes no parameter or a single time period parameter.
|
DAYS | |
 |
Return e raised to the nth power, where e = 2.71828183. This function is the inverse of the LOG function, which returns the logarithm of the argument. |
EXP | |
 |
Return the first of at least two arguments that is non-zero. This function can be used to avoid a complex conditional: FIRSTNONZERO(a, b, c) instead of IF a <> 0 THEN a ELSE IF b <> 0 THEN b ELSE IF c <> 0 THEN c ELSE 0 If any argument is NaN this will also be skipped, but Infinity and -Infinity are treated as non-zero values. These non-numeric values do not occur often, but can crop up as the result of certain calculations e.g. DIVIDE(1, 0), DIVIDE(0, 0), DIVIDE(-1, 0) |
FIRSTNONZERO | |
 |
Return the summary Half-Year value of the source line item or property which matches the Time Summary method set for the source. | HALFYEARVALUE | |
 |
Return a Boolean indicating if a value in the source (first parameter) is the first occurrence of that value within the source along a specified dimension of the source (second parameter). | ISFIRSTOCCURRENCE | |
 |
Return the item of a list formatted or time period formatted line item. | ITEM | |
 |
Return the value from a specified line item a number of periods later than the selected cell. A mode argument controls whether the number of periods can be a non-positive value. |
LEAD | |
|
Return the number of characters in a text string. | LEN | |
|
Return the number of characters in a text string. | LENGTH | |
 |
Return the natural logarithm of a number, based on the constant e. | LN | |
 |
Return the logarithm of a number to the specified base. | LOG | |
 |
Return the minimum value in the selected line items. | MIN | |
 |
Return just the remainder when one integer is divided by another. | MOD | |
 |
Return a cumulative sum across a month time range for a single numeric parameter then resets. | MONTHTODATE | |
 |
Return the Summary month value of the source line item or property that matches to the Time Summary method set for the source. | MONTHVALUE | |
 |
Return a value from the next period. | NEXT | |
 |
Return a value from the next version. | NEXTVERSION | |
 |
Return the value of a line item from a specified number of periods in advance, or an alternative value or line item if those periods are off the end of the timescale. If the specified number of periods is negative, take the value from earlier. |
OFFSET | |
 |
Return the parent of a passed list, or the parent time period of a passed time period. | PARENT | |
 |
Return the value designated by x using the offset n, which may be a positive (past) or negative (future) offset. | POST | |
 |
Return the value from the previous period. | PREVIOUS | |
 |
Return the value from the previous version. | PREVIOUSVERSION | |
 |
Return the quarter value of the source line item or property, that matches the time summary method set for the source. | QUARTERVALUE | |
 |
Return a value based on the sign of a number. The function will return -1 if negative, 1 if positive, and 0 if the number is zero. |
SIGN | |
 |
Return a number 1 to 7 for the day-of-week number when passed a date. | WEEKDAY | |
 |
Return cumulative sum across a week time range from a single numeric parameter as source then resets. | WEEKTODATE | |
 |
Return the maximum value for a specified line itemof one of the dimensions of the target. The source uses a list formatted item for this dimension. | x[MAX:y] | |
 |
Return the minimum value for a specified line item of one of the dimensions of the result/target when used as an aggregation function. The source uses a list formatted line item for this dimension. | x[MIN:y] | |
 |
Return cumulative sum across a year time range from a single numeric parameter as source, then resets. | YEARTODATE | |
 |
Return the year value of the source line item or property, according to the time summary method set for the source. | YEARVALUE | |
 |
Return the maximum value from two or more parameters | MAX | |
 |
Return the name of the list item, or time period, of a passed list item, or time period, as text. | NAME | |
| Round |  |
Round to the nearest multiple of n. | MROUND |
 |
Round x to n decimal places. | ROUND | |
| Select |  |
Select a specific item from one or more of the hierarchy list, then sum values based on a list formatted line item, or property, in the source that matches a dimension in the target. | SUM AND SELECT |
 |
Select a specific item from the hierarchy list. | SELECT | |
 |
Select a specific item from the hierarchy list, then looks up a value based on matching that selection. | SELECT and LOOKUP | |
| Show |  |
Show the number of days in a specified calendar month. | DAYSINMONTH |
 |
Show the number of days in a specified year where year is formatted as a four digit number, YYYY. | DAYSINYEAR | |
 |
Show the last date in the specified period. Can take no parameters or one time period parameter:
|
END | |
 |
Show the first non-blank text, list or date cell and uses the mapping to determine where to post the result. FIRSTNONBLANK is available as an aggregation functions in a selection clause, to be consistent with being available as a summary method for Date, List, Time Period, and Text formats. | FIRSTNONBLANK | |
 |
Show the last non-blank text, list or date cell and uses the mapping to determine where to post the result. LASTNONBLANK is available as an aggregation function in a selection clause, to be consistent with having it available as summary methods for Date, List, Time Period, and Text formats. |
LASTNONBLANK | |
 |
Show the first date in the period. | START | |
| Spread |  |
Spread data values forward over time by applying the specified profile, this function allocates a value over the next few periods based on a table containing % current period, % next period, % pd+2 etc. | PROFILE |
 |
Spread a value evenly over a number of periods. | SPREAD | |
| Sum |  |
Sum based on a list formatted line item, or property, in the source, then look up a value from a source module using one or more mappings. | SUM and LOOKUP |
 |
Sum a single numeric parameter across a Quarter time period, and then reset. | QUARTERTODATE | |
 |
Sum values based on a list-formatted, or time period-formatted, line item or property in the source that matches a dimension in the target. | SUM | |
 |
Sum the week value of the source line item, or property, according to the Time Summary method set for the source. | WEEKVALUE | |
| Test |  |
Take two list or time period formatted parameters and test:
|
ISANCESTOR |
 |
Test to see if all conditions are met. | AND | |
 |
Used with conditional arguments to set the result to BLANK if the test condition is not satisfied. Set the result to BLANK if column is formatted as text, list, time period, or date formatted. | BLANK | |
 |
Test an argument for truth and return one of two values based on the result. | IF THEN ELSE | |
 |
Test an argument for truth and return a value or BLANK based on the result. When the result is text, list-formatted, date or Boolean, the result can be set to be BLANK. | IF THEN ELSE BLANK | |
 |
Test an argument and return a value, or begin a second test based on the result. This function can be nested multiple times to test more than one condition. | IF THEN ELSE IF... | |
 |
Test to see if a date, text, time period, or list formatted cell is not blank. | IF... ISNOTBLANK | |
 |
Test whether two or more conditions are met. | IF...AND | |
 |
Test whether condition is not TRUE. | IF...NOT | |
 |
Test if any of two or more conditions is met. Return 1 when either x or y is TRUE. Otherwise result is 0 (zero). |
IF...OR | |
 |
Test whether a date, text, time period, or list formatted cell is blank. | IF… ISBLANK | |
 |
Test whether a date falls within the period specified on the time dimension or falls within a time period. Return a Boolean result.
|
INPERIOD | |
 |
Test to see if the version you are on is the Actual version. Return a Boolean result. | ISACTUALVERSION | |
 |
Test whether an item is blank (the item must be date, text, list, or time period formatted). Return a Boolean result. | ISBLANK | |
 |
Test to see if the Version is Current based on the flag that you have set in Settings > Versions. | ISCURRENTVERSION | |
 |
Test to see if an item is not blank, where the item is date, text, list, or time period formatted. Return a Boolean result. | ISNOTBLANK | |
 |
Test to see if a condition is not met. | NOT | |
 |
Test to see if any of two or more conditions is met. | OR |