Some functions have differences in functionality, or are not available, in Polaris.
Title | Syntax | Description |
---|---|---|
ABS Numeric Functions | ABS(Number) | |
ADDMONTHS Time and Date Functions | ADDMONTHS(Date, number) | |
ADDYEARS Time and Date Functions | ADDYEARS(Date, number) | |
AGENTS Call Center Planning Functions | AGENTS(SLA, Target response time, Arrival rate, Average duration) | |
AGENTSB Call Center Planning Functions | AGENTSB(SLA, Arrival rate, Average duration) | |
ALL Aggregation Functions | Source[ALL: Mapping, ALL: Mapping 2, etc.] | |
ANSWERTIME Call Center Planning Functions | ANSWERTIME(Number of servers, SLA, Arrival rate, Average duration) | |
ANY Aggregation Functions | Source[ANY: Mapping, ANY: Mapping 2, etc.] | |
ARRIVALRATE Call Center Planning Functions | ARRIVALRATE(Number of servers, SLA, Target response time, Average duration) | |
AVERAGE Aggregation Functions | Values to average[AVERAGE: Mapping, AVERAGE: Mapping 2, etc.] | |
AVGDURATION Call Center Planning Functions | AVGDURATION(Number of servers, SLA, Target response time, Arrival rate) | |
AVGWAIT Call Center Planning Functions | AVGWAIT(Number of servers, Arrival rate, Average duration) | |
CODE Miscellaneous Functions | CODE(Item) | |
COLLECT Miscellaneous Functions | COLLECT() | |
COMPARE Logical Functions | COMPARE(Text to compare 1, Text to compare 2 [, Comparison mode] [, Locale]) | |
COUPDAYBS Financial Functions | COUPDAYBS(Settlement, Maturity, Frequency[, Basis]) | |
COUPDAYS Financial Functions | COUPDAYS(Settlement, Maturity, Frequency[, basis]) | |
COUPDAYSNC Financial Functions | COUPDAYBS(Settlement, Maturity, Frequency[, basis]) | |
COUPNCD Financial Functions | COUPNCD(Settlement, Maturity, Frequency) | |
COUPNUM Financial Functions | COUPNUM(Settlement, Maturity, Frequency) | |
COUPPCD Financial Functions | COUPPCD(Settlement, Maturity, Frequency) | |
CUMIPMT Financial Functions | CUMIPMT(Interest rate, Number of periods, Principal, Start period, End period [, Timing]) | |
CUMPRINC Financial Functions | CUMPRINC(Interest rate, Number of periods, Loan balance, Start period, End period [, Timing]) | |
CUMULATE Time and Date Functions | CUMULATE (Values to add [, Boolean] [, List]) | |
CURRENTPERIODEND Time and Date Functions | CURRENTPERIODEND() | |
CURRENTPERIODSTART Time and Date Functions | CURRENTPERIODSTART() | |
CURRENTVERSION Miscellaneous Functions | CURRENTVERSION(Line item) | |
DATE Time and Date Functions | DATE(Year, Month, Day) | |
DAY Time and Date Functions | DAY(Date) | |
DAYS Time and Date Functions | DAYS([Period]) | |
DAYSINMONTH Time and Date Functions | DAYSINMONTH(Year, Month) | |
DAYSINYEAR Time and Date Functions | DAYINYEAR(Year) | |
DECUMULATE Time and Date Functions | DECUMULATE(Value to subtract) | |
DIVIDE Numeric Functions | DIVIDE(Dividend, Divisor) | |
DURATION Financial Functions | DURATION(Settlement, Maturity, Rate, Yield, Frequency [, Basis]) | |
END Time and Date Functions | END([Time period]) | |
ERLANGB Call Center Planning Functions | ERLANGB(Number of servers, Arrival rate, Average duration) | |
ERLANGC Call Center Planning Functions | ERLANGC(Number of servers, Arrival rate, Average duration) | |
EXP Numeric Functions | EXP(Number) | |
FIND Text Functions | FIND(Text to find, Text to search [, Starting character]) | |
FINDITEM Miscellaneous Functions | FINDITEM(List or Time, Item to find) | |
FIRSTNONBLANK Aggregation Functions | Line item to search [FIRSTNONBLANK: Mapping, FIRSTNONBLANK: Mapping 2, etc.] | |
FIRSTNONZERO Numeric Functions | FIRSTNONZERO(Value 1, Value 2, [etc.]) | |
FV Financial Functions | FV(Interest rate, Number of periods, Payments [, Present value] [, Payment timing]) | |
HALFYEARTODATE Time and Date Functions | HALFYEARTODATE(Line item to cumulate) | |
HALFYEARVALUE Time and Date Functions | HALFYEARVALUE(Line item) | |
IF THEN ELSE Logical Functions | IF Boolean argument THEN Result 1 ELSE Result 2 | |
INPERIOD Time and Date Functions | INPERIOD(Date to test, Time period) | |
IPMT Financial Functions | IPMT(Interest rate, Period to examine, Number of periods, Present value [, Future value] [, Payment timing]) | |
IRR Financial Functions | IRR(Cash flow [, Estimate]) | |
ISACTUALVERSION Logical Functions | ISACTUALVERSION() | |
ISANCESTOR Logical Functions | ISANCESTOR(Ancestor, Descendant) | |
ISBLANK Logical Functions | ISBLANK(Value to test) | |
ISCURRENTVERSION Logical Functions | ISCURRENTVERSION() | |
ISFIRSTOCCURRENCE Logical Functions | ISFIRSTOCCURRENCE(Values to compare, List dimension to search) | |
ISNOTBLANK Logical Functions | ISNOTBLANK(Value to test) | |
ITEM Miscellaneous Functions | ITEM(List or Time) | |
LAG Time and Date Functions | LAG(Value to offset, Offset amount, Substitute value [, Non-positive behavior]) | |
LASTNONBLANK Aggregation Functions | Line item to search[LASTNONBLANK: Mapping, LASTNONBLANK: Mapping 2, etc.] | |
LEAD Time and Date Functions | LEAD(Value to offset, Offset amount, Substitute value [, Non-positive behavior]) | |
LEFT Text Functions | LEFT(Text [, Number of characters]) | |
LENGTH Text Functions | LENGTH(Text to evaluate) | |
LN Numeric Functions | LN(Number) | |
LOG Numeric Functions | LOG(Number, Base) | |
LOOKUP Logical Functions | Values to lookup[LOOKUP: Mapping, LOOKUP: Mapping 2] | |
LOWER Text Functions | LOWER(Text [, Locale]) | |
MAILTO Text Functions | MAILTO(Display text, To [, CC] [, BCC] [, Subject] [, Body text]) | |
MAKELINK Text Functions | MAKELINK(Display text, URL) | |
MAX Aggregation Functions | Source[MAX: Mapping, MAX: Mapping 2, etc.] | |
MAX Numeric Functions | MAX(Value to compare, Value to compare 2, [etc.]) | |
MDURATION Financial Functions | MDURATION(Settlement, Maturity, Rate, Yield, frequency [, basis]) | |
MID Text Functions | MID(Text, Start position [, Number of characters]) | |
MIN Aggregation Functions | Source[MIN: Mapping, MIN: Mapping 2, etc.] | |
MIN Numeric Functions | MIN(Value to compare, Value to compare 2, [etc.]) | |
MOD Numeric Functions | MOD(Dividend, Divisor) | |
MONTH Time and Date Functions | MONTH(Value to convert, [Time period method]) | |
MONTHTODATE Time and Date Functions | MONTHTODATE(Line item to aggregate) | |
MONTHVALUE Time and Date Functions | MONTHVALUE(Line item) | |
MOVINGSUM Time and Date Functions | MOVINGSUM(Line item to aggregate [, Start period] [, End period] [, Aggregation method]) | |
MROUND Numeric Functions | MROUND(Number to round [, Multiple to round to] [, Rounding direction]) | |
NAME Text Functions | NAME(List item) | |
NEXT Time and Date Functions | NEXT(Expression) | |
NEXTVERSION Miscellaneous Functions | NEXTVERSION(Expression) | |
NPER Financial Functions | NPER(Interest rate, Payments, Present value [, Residual value] [, Timing]) | |
NPV Financial Functions | NPV(Discount rate, Cash flow, Dates, Transactions) | |
OFFSET Time and Date Functions | OFFSET(Value to offset, Offset amount, Substitute value) | |
PARENT Miscellaneous Functions | PARENT(Child value) | |
PERIOD Time and Date Functions | PERIOD(Date) | |
PMT Financial Functions | PMT(Interest rate, Number of periods, Present value [, Future value] [, Timing]) | |
POST Time and Date Functions | POST(Value to post, Offset amount) | |
POWER Numeric Functions | POWER(Number, Power) | |
PPMT Financial Functions | PPMT(Interest rate, Period to examine, Number of periods, Present value [, Future value] [, Timing]) | |
PREVIOUS Time and Date Functions | PREVIOUS(Expression) | |
PREVIOUSVERSION Miscellaneous Functions | PREVIOUSVERSION(Expression) | |
PRICE Financial Functions | PRICE(Settlement, Maturity, Rate, Yield, Redemption, Frequency[, Basis]) | |
PROFILE Time and Date Functions | PROFILE(Number to change, Profile) | |
PV Financial Functions | PV(Interest rate, Number of periods, Payments, Future value, Payment timing) | |
QUARTERTODATE Time and Date Functions | QUARTERTODATE(Line item to aggregate) | |
QUARTERVALUE Time and Date Functions | QUARTERVALUE(Line item) | |
RANK Miscellaneous Functions | RANK(Source values [, Direction] [, Equal value behavior] [, Include value] [, Ranking groups]) | |
RANKCUMULATE Miscellaneous Functions | RANKCUMULATE(Cumulation values, Ranking values [, Direction] [, Include value] [, Ranking groups]) | |
RATE Financial Functions | RATE(Number of periods, Payments, Present value[, Future value] [, Payment timing] [ ,Rate estimate]) | |
RIGHT Text Functions | RIGHT(Text [, Number of characters]) | |
ROUND Numeric Functions | ROUND(Number to round [, Number of decimal places] [, Rounding direction] [, Rounding method]) | |
SELECT Logical Functions | Source[SELECT: Target item] | |
SIGN Numeric Functions | SIGN(Number) | |
SLA Call Center Planning Functions | SLA(Number of servers, Target response time, Arrival rate, Average duration) | |
SPREAD Time and Date Functions | SPREAD(Value to divide, Number of time periods) | |
SQRT Numeric Functions | SQRT(Number) | |
START Time and Date Functions | START(Time period) | |
SUBSTITUTE Text Functions | SUBSTITUTE(Text to search in, Text to find, Replacement text) | |
SUM Aggregation Functions | Values to sum[SUM: Mapping, SUM: Mapping 2, etc.] | |
TEXT Text Functions | TEXT(Number to convert) | |
TEXTLIST Aggregation Functions | Values to list[TEXTLIST: Mapping, TEXTLIST: Mapping 2, etc.] | |
TEXTLIST Text Functions | TEXTLIST(Text to concatenate, Separator, List to reference [, Duplicate behavior]) | |
TIMESUM Time and Date Functions | TIMESUM(Line item to aggregate [, Start period] [, End period] [, Aggregation method]) | |
TRIM Text Functions | TRIM(Text) | |
UPPER Text Functions | UPPER(Text [, Locale]) | |
VALUE Miscellaneous Functions | VALUE(Value to convert) | |
WEEKDAY Time and Date Functions | WEEKDAY(Date [, First day of the week]) | |
WEEKTODATE Time and Date Functions | WEEKTODATE(Line item to aggregate) | |
WEEKVALUE Time and Date Functions | WEEKVALUE(Line item) | |
YEAR Time and Date Functions | YEAR(Value to convert, [Time period method]) | |
YEARFRAC Financial Functions | YEARFRAC(Start, End[, Basis]) | |
YEARTODATE Time and Date Functions | YEARTODATE(Line item) | |
YEARVALUE Time and Date Functions | YEARVALUE(Line item) | |
YIELD Financial Functions | YIELD(Settlement, Maturity, Rate, Price, Redemption, Frequency [, Basis]) |
The data retrieved by aggregation functions is based on one or more mappings. Each mapping is a line item or property with the list data type. This list must also apply to the source line item. The values of the mappings determine which values to retrieve.
Each aggregation function uses a distinct syntax in the following format:
Data source[Function: Mapping 1, Function: Mapping 2, etc.]
Typically, you use call center planning functions to predict the operational performance of a call center. They calculate the number of agents required to meet a service level agreement based on certain baseline assumptions, such as:
- Incoming call rate
- Average call duration
- Number of available agents
Call center planning functions are unavailable in Polaris. Learn more about the differences between Anaplan calculation engines.
There are several general financial functions in Anaplan. These include CUMIPMT, CUMPRINC, FV, IPMT, IRR, NPER, NPV, PMT, PPMT, PV, and RATE.
Anaplan also offers investment management functions. These include COUPDAYBS, COUPDAYS, COUPDAYSNC, COUPNCD, COUPNUM, COUPPCD, DURATION, MDURATION, PRICE, YEARFRAC, and YIELD.
Investment management functions enable you to perform common calculations relating to the price and yield of bonds and the coupon periods that define when the bondholder receives interest as the bond matures.
Many investment management functions rely on day count conventions to determine the number of days between two dates. Anaplan defaults to a modified version of the US 30/360 day count convention, but you can choose to use other day count conventions.
Most financial functions are currently unavailable in Polaris. Learn more about the differences between Anaplan calculation engines.
Text-formatted values enable you to collaborate with and capture insights from people who use the models you build. Use the text functions available in Anaplan to effectively work with this data.
You can use text functions to extract strings from text, find specific characters, concatenate text values, convert values to text, and more. See the full list of text functions in the table below.
When two line items have a different time scale, and a formula uses both, values aggregate into the greater timescale. For example, days aggregate into months and months into quarters. You can choose to skip levels. For example, you can aggregate days into quarters. Learn more in Time aggregation.
The following functions are called time series functions: CUMULATE, DECUMULATE, LAG, LEAD, MOVINGSUM, OFFSET, POST, PREVIOUS, and PROFILE. If you use line items as arguments for these functions, the source and target line items must use the same time range.
Title | Description |
ADDMONTHS | The ADDMONTHS function adds a number of months to a date. |
ADDYEARS | The ADDYEARS function adds a number of years to a date. |
CUMULATE | The function CUMULATE adds values and returns a number. By default, the values are added across a time period. You can also choose to add values across a list. |
CURRENTPERIODEND | The CURRENTPERIODEND function returns the end date from a model's current period. |
CURRENTPERIODSTART | The CURRENTPERIODSTART function returns the start date from a model's current period. |
DATE | The DATE function forms a date from values that represent the year, month, and day. |
DAY | The DAY function returns the day from a date as a number between 1 and 31. If the day is blank, it returns 0. |
DAYS | The DAYS function returns the number of days in a given time period. |
DAYSINMONTH | The DAYSINMONTH function returns the number of days in a month you specify. |
DAYSINYEAR | The DAYSINYEAR function returns the number of days in a year you specify. |
DECUMULATE | The DECUMULATE function calculates the difference between the values of the current and previous periods. |
END | The END function returns the last date of a time period. |
HALFYEARTODATE | The HALFYEARTODATE function cumulates values from a single numeric parameter, over a half-year period. The HALFYEARTODATE cumulation starts at the Fiscal Year Start Month as selected in the Model Calendar, and resets every half-year. |
HALFYEARVALUE | The HALFYEARVALUE function references another line item and returns the half-yearly time summary in place of the detail value. |
INPERIOD | The INPERIOD function returns a TRUE result for a date that falls under a time period or a module's Time dimension. It returns FALSE for all other dates. |
LAG | LAG returns a value from a period in the past. |
LEAD | LEAD returns a value from a period in the future. |
MONTH | The function MONTH converts a date or time period to a month in number format. |
MONTHTODATE | MONTHTODATE cumulates values from a single numeric parameter, within a monthly time range. |
MONTHVALUE | The MONTHVALUE function references another line item and returns the monthly time summary in place of the detail value. |
MOVINGSUM | MOVINGSUM returns values over a changing time range. For each time range included, it aggregates the sum of values found. |
NEXT | The NEXT function returns the value from the next period in the Time dimension. |
OFFSET | OFFSET returns a value from a period before or after the current period. |
PERIOD | The PERIOD function converts a date to a time period. |
POST | The POST function pushes a value a set number of periods into the future. |
PREVIOUS | The PREVIOUS function returns the value from the previous period in the Time dimension. |
PROFILE | The PROFILE function multiplies values over time based on a series of numbers, or profile. The profile does not require a time dimension. |
QUARTERTODATE | QUARTERTODATE accumulates values from a single numeric parameter, within a quarterly time range. |
QUARTERVALUE | The QUARTERVALUE function references another line item and returns the quarterly time summary in place of the detail value. |
SPREAD | The SPREAD function divides a value evenly over a number of time periods. |
START | The START function returns the first date of a time period. |
TIMESUM | Aggregates values between two time periods. |
WEEKDAY | The WEEKDAY function converts a date to a number between one and seven, representing the day of the week. |
WEEKTODATE | WEEKTODATE aggregates the daily values within a week from a single numeric parameter. WEEKTODATE resets after the last day of the week. |
WEEKVALUE | The WEEKVALUE function references another line item and returns the weekly time summary in place of the detail value. |
YEAR | The function YEAR converts a date or time period to a year in number format. |
YEARTODATE | YEARTODATE cumulates values from a single numeric parameter, within a yearly time range. YEARTODATE resets at each yearly start date, based on Calendar Type. |
YEARVALUE | The YEARVALUE function references another line item and returns the yearly time summary in place of the detail value. |