Aggregation functions

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

Call center planning functions

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.

Financial functions

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.

Logical functions

Miscellaneous functions

Numeric functions

Text functions

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.

Time and date functions

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.

Argument	Data type	Description
Number	Number	The number to determine the sign of.

Formula	Result
`SIGN(100)`	1
`SIGN(0)`	0
`SIGN(-10)`	-1

Arguments

Excel equivalent

Examples