Use miscellaneous functions to perform a range of calculations with your data.

TitleDescription
CODE

The CODE function returns a list item's code.

COLLECT

Use the COLLECT function in a module that includes a line item subset to pull the source line item values into the module.

CURRENTVERSION

The CURRENTVERSION function returns the value from another line item for the version that is set as Current in a model.

FINDITEM

The FINDITEM function searches for a text value within the names and codes of the items or time periods in a given list or Time respectively. If the FINDITEM function finds a match, it returns the corresponding list item or time period.

ITEM

When used with a list, the ITEM function returns the list item that applies to each cell. When used with Time, it returns the time period that applies to each cell.

NEXTVERSION

The NEXTVERSION function evaluates the given expression using the next version.

PARENT

The PARENT function returns the parent item of list items and time periods.

PREVIOUSVERSION

The PREVIOUSVERSION function evaluates the given expression using the previous version.

RANK

The RANK function evaluates a set of values and assigns sequential rankings starting at 1.

RANKCUMULATE

The RANKCUMULATE function ranks values and then cumulates values in order of the ranking. It can perform ranking separately across different groups.

VALUE

The VALUE function converts text values that represent numbers to numbers.

CODE

For example, you can use the CODE function to ensure that a formula only applies to a specific employee in a list.

Syntax

CODE(Item)

Arguments

ArgumentData typeDescription
ItemList or time periodThe list item or time period to return the code of.

The CODE function returns a text value. If a list item does not have a code, the CODE function returns a blank value.

Additional information

When adding codes to list items, you can manually add or import codes from the Grid View of the corresponding list.

Calculation engine functionality differences

In Polaris, if you use a time period value for the Item argument, the CODE function returns a blank text result.

In the Classic Engine, if you use a time period value for the Item argument, the CODE function returns the time period value as text.

Excel equivalent function

LOOKUP

Examples

Example usage with lists as dimensions

In this example, a Sales Commission Rates module has line items on rows and the Sales Personnel list on columns. The Personnel Code line item has a text data type.

The formula uses the ITEM function to identify the list item in the Sales Personnel list, and the CODE function to return the code of that list item.


John JohnsonBarbara JonesHannah Smith
Length of Service (years)682
Commission Rate %3.25%3.5%2%

Personnel Code

CODE(ITEM(Sales Personnel))

SP001SP002SP003

Example usage with list-formatted line items

In this example, an Income Statement module has line items on rows, time on columns, and the Regions list on pages. The Personnel Code line item has a text data type. The Sales Staff line item has a list data type on the Sales Personnel list.

The formula uses the CODE function to return the code of the corresponding list item in Sales Staff.


Jan 21Feb 21Mar 21Apr 21May 21Jun 21
Sales StaffJohn JohnsonJohn JohnsonJohn JohnsonHannah SmithHannah SmithHannah Smith
Sales Commission 20,36610,30013,42220,0021,99915,698

Personnel Code

CODE(Sales staff)

SP001SP001SP001SP003SP003SP003
COLLECT

For example, you might want to compare 2 months and view the variance between those months for different line items.

To create a comparison, create a line item subset to group line items from different modules in one place. Then, create a staging module and pull the data from the line items into the module using the COLLECT function. 

Syntax

COLLECT()

Arguments

The COLLECT function does not use any arguments.

This function returns numbers.

Additional information

The source modules must contain the line items in the line item subset used in the result module. The result module must have a line item subset as a dimension.

See Line item subset example for more information.

Examples

General example

The COLLECT function can be used to collect values from a line item subset. In this example, the two source modules for the line item subset are named P&L and Fixed assets. The aggregated total for FY22 from the Time dimension displays as the single column in both modules, and line items on rows.

P&L module:


FY15
Sales last year1200000
% increase5%
Sales1260000
Margin %40%
Cost of Sales756000
Gross Margin2016000
Headcount10
Cost per employee
Staff Costs360000
Rent & Rates12000
Utilities12000
Marketing12000
IT costs12000
Total Overheads408000
Operating Profit1608000

Revenue module:


FY15
Assets Sold720000
Buildings120000
Fixtures120000
Software120000
Patents120000
Assets Purchased480000

The line items selected for the line item subset display as below within Line item subsets . The line item subset is named Receipts and Payments.


Receipts and Payments
P&L
      Sales last year
      % increase
      Sales 
      Margin %
      Cost of Sales
   Gross Margin
      Headcount
      Cost per employee
      Staff Costs
      Rent & Rates
      Utilities
      Marketing
      IT costs
      Total Overheads
   Operating Profit
Fixed assets
      Assets Sold
      Buildings
      Fixtures
      Software
      Patents
      Assets Purchased

The above line item subset is used for the rows dimension in a Receipts and Payments module. FY22 from the Time dimension is on columns, and a single line item is on the pages dimension. The line item contains this formula: COLLECT(). This collects the values from the P&L and Fixed Assets source modules:


FY22
Sales1260000
Cost of Sales756000
Gross Margin2016000
Staff Costs360000
Rent & Rates12000
Utilities12000
Marketing12000
IT costs12000
Total Overheads408000
Operating Profit1608000
Assets Sold720000
Buildings120000
Fixtures120000
Software120000
Patents120000
Assets Purchased480000

Line item subset example

The Variance report staging module step of the Line item subset example contains an example of how you can use the COLLECT function.

CURRENTVERSION

For example, you can use the CURRENTVERSION function to compare data between versions.

Syntax

CURRENTVERSION(Line item)

Arguments

ArgumentData typeDescription
Line itemNumber, Boolean, date, time period, list, or textThe line item to return the value from the current version for.

The CURRENTVERSION function returns a result of the same data type as the Line item argument.

Syntax example

CURRENTVERSION(Net profit)

In this example, the formula returns the value from the current version for the Net profit line item.

Constraints

  • You can only use the CURRENTVERSION function if a version is set as Current in Versions in the model settings bar.
  • You can only use the CURRENTVERSION function if all line items referenced in a formula use Versions as a dimension.

Examples

In this example, an income statement module has line items on rows, the Products list on columns, and versions on pages. The Budget version displays as a page selector.

The model that contains the module has three versions: Budget, Actual, and Forecast. The Actual version is set as Current.

The formula uses the CURRENTVERSION function to show the difference between budget and actual data for Cost of Goods.


ChocolatesSoursTaffyFudge
Cost of Goods190,000150,000250,000180,000

Cost of Goods (Actual)

CURRENTVERSION(Cost of Goods)

198,000140,433250,670203,005
FINDITEM

For example, you could use the FINDITEM function to check if an item available in a certain region is available in other regions.

Syntax

FINDITEM(List or Time, Item to find)

Arguments

ArgumentData typeDescription
List or TimeList or Time dimension

The list to search within for the Item to find argument.

You can also use the time dimension and search for periods.

Item to findTextThe text to match against the name or code of the list item or time period to find.

The FINDITEM function returns a result of the list or time period data type, based on the given List or Time argument.

Syntax example

FINDITEM(USA Products, Product Name)

Constraints

Configuration of the result line item

If the List or Time argument is a list, the result line item must have the list data type, configured with the same list.

If the List or Time argument uses Time, the result line item must have the time period data type. The result the FINDITEM function returns depends on the time period granularity (for example, Month, or Quarter) of the line item.

Calculation engine functionality differences

In Polaris, you cannot use the FINDITEM function with one Time argument.

In the Classic Engine, you can use FINDITEM with one or two Time arguments.

Excel equivalent

LOOKUP

Examples

These examples refer to a Products list. The Products list contains the following items, as seen in the Grid View tab in General Lists.


ParentCodeFruit subsetVegetable subset
ApplesFruitAPP001
PeachesFruitPEA002
BananasFruitBAN003
FruitTotal Products

CarrotsVegetablesCAR004
CucumbersVegetablesCUC005
LettuceVegetablesLET006
VegetablesTotal Products

Total Products

List example

In this example, time is on columns, and there are three line items on rows.

The top-selling product for each month is manually entered into the Top-selling product line item on a dashboard. The Fruit product top seller? and Vegetable product top seller? line items use the FINDITEM function to return the top-selling product if it is a fruit or vegetable respectively.


Jan 22Feb 22Mar 22Apr 22May 22Jun 22
Top-selling productApplesBananasCucumbersCucumbersPeachesLettuce

Fruit product top seller?

FINDITEM(Fruit subset, 'Top-selling product')

ApplesBananas

Peaches

Vegetable product top seller?

FINDITEM(Vegetable subset, 'Top-selling product')



CucumbersCucumbers
Lettuce

List with codes example

This example demonstrates how codes for list items can be used to find items, instead of the text in list item names.


Jan 22Feb 22Mar 22Apr 22May 22Jun 22
Code of top-selling productAPP001BAN003CUC005CUC005PEA002LET006

Fruit product top seller?

FINDITEM(Fruit subset, 'Code of top-selling fruit product')

ApplesBananas

Peaches

Vegetable product top seller?

FINDITEM(Vegetable subset, 'Code of top-selling product')



CucumbersCucumbers
Lettuce
ITEM

For example, you can use the ITEM function to create conditional formulas where the output changes based on the list that applies to a cell in a module.

Syntax

ITEM(List or Time)

Arguments

ArgumentData TypeDescription
List or TimeList or Time dimensionThe name of the list, or a reference to Time, to return the item from.

The ITEM function returns a result of the list or time period data type, based on the given List or Time argument.

Syntax example

ITEM(Products)

Here, the ITEM function returns the applicable value for the Products list, which is a dimension in the module.

Additional information

You can use two arguments separated by a comma for the ITEM function. When you do this, your formula automatically updates to use the FINDITEM function upon submission.

For example:

  • The formula ITEM(Hardware, "Bolts") updates to become FINDITEM(Hardware, "Bolts").
  • The formula ITEM(Time, "Jan 22") updates to become FINDITEM(Time, "Jan 22").

Constraints

  • You cannot use the ITEM function with the Formula summary method. This is because the parent list is not selected in Applies To, only the child list.
  • The list that you use the ITEM function with must be a dimension selected in Applies To in the module.

Calculation engine functionality differences

In Polaris, you cannot use ITEM in formulas of line items with formula summary method. In the Classic Engine, you can.

In Polaris, you cannot use the ITEM function with one Time argument. In the Classic Engine, you can.

Examples

Simple example

In this example, Time is on columns and a Products list is on rows. The page dimension contains two line items, which contain the formulas ITEM(Time) and ITEM(Products).

The table below displays the result of the ITEM(Time) formula.


Jan 22Feb 22Mar 22
Product 1Jan 22Feb 22Mar 22
Product 2Jan 22Feb 22Mar 22
Product 3Jan 22Feb 22Mar 22
Product 4Jan 22Feb 22Mar 22

The table below displays the result of the ITEM(Products) formula.


Jan 22Feb 22Mar 22
Product 1Product 1Product 1Product 1
Product 2Product 2Product 2Product 2
Product 3Product 3Product 3Product 3
Product 4Product 4Product 4Product 4

The ITEM function with IF THEN ELSE

You can use the ITEM function with the IF THEN ELSE function to create conditional formulas that change depending upon the applicable list item.

Take for example a module dimensioned by two lists named Products and Organization. If you require a calculation for a specific product, or products, you can use the formulas as below:

IF ITEM(Products) = Products.Product 1 THEN (Calculation for Product 1) ELSE (Calculations for other products)

IF ITEM(Products) = Products.Product 1 OR Products.Product 2 THEN (Calculation for Product 1 or Product 2) ELSE (Calculations for other products)

Additionally, you can specify a calculation that only applies to a specific combination of the Product and Organization lists by using the AND operator:

IF ITEM(Products) = Products.Product 1 AND ITEM(Organization) = Organization.Company 1 THEN (Calculation for Product 1 and Company 1) ELSE (Calculations for other products and companies)

Example with list

In this example, the Organization list is on columns, and the Class line item on rows. The Class line item has the list data type, and references the Company Class list. The Class line item uses the ITEM function to assign certain items in the Organization list a different value from the Company Class list. The formula used is:

IF ITEM(Organization) = Organization.'Company 01' OR Organization.'Company 05' THEN Company Class.Class A ELSE IF ITEM(Organization) = Organization.'Company 08' THEN Company Class.Class C ELSE Company Class.Class B


Company 01Company 02Company 03Company 04Company 05Company 06Company 07Company 08
ClassClass AClass BClass BClass BClass AClass BClass BClass C

Example with time

In this example, time displays on columns, and the Product Release Status line item on rows. The Product Release Status line item has the list data type, and references the Product Status list. The Product Release Status line uses the ITEM function to assign different time periods a different value from the Product Status list, based on time period. The formula used is:

IF ITEM(Time) < TIME.'Apr 21' THEN Product Status.Before Release ELSE Product Status.After Release


Jan 21Feb 21Mar 21Apr 21May 21Jun 21Jul 21Aug 21Sep 21Oct 21Nov 21Dec 21
Product Release StatusBefore ReleaseBefore ReleaseBefore ReleaseAfter ReleaseAfter ReleaseAfter ReleaseAfter ReleaseAfter ReleaseAfter ReleaseAfter ReleaseAfter ReleaseAfter Release
NEXTVERSION

For example, you can use the NEXTVERSION function to make comparisons between data from different versions of a plan.

Syntax

NEXTVERSION(Expression)

Arguments

ArgumentData typeDescription
ExpressionNumber, Boolean, date, time period, list, or textThe expression to return the value from the next version for.

The NEXTVERSION function returns a result of the same data type as the Expression argument.

Syntax example

NEXTVERSION(Operating costs)

In this example, the formula returns the value from the next version of the Operating costs line item.

Additional information

The order of versions is from top to bottom in Versions  in the model settings bar. 

For example, if your model's versions are ordered Budget, Actual, and Forecast, the next version of Actual is Forecast. If there is no next version, the function returns a blank value or 0.

Constraints

The result cell that you use the NEXTVERSION function in must use Versions as a dimension.

Examples

In this example, an income statement module has line items on rows, time on columns, and versions on pages. The model has three versions: Budget, Actual, and Forecast, in that order. The page selector is set to the Actual version.

The formula in the Forecast Revenue Costs line item uses the NEXTVERSION function to retrieve the value of Revenue Costs from the Forecast version, which is the next version.


Jan 21Feb 21Mar 21
Revenue Costs12,45713,98713,483
Salary Costs27,95028,00022,765

Forecast Revenue Costs

NEXTVERSION(Forecast Revenue Costs)

15,00015,50016,000
PARENT

For example, you can use PARENT to see which quarter a month belongs to.

Syntax

PARENT(Child value)

Arguments

ArgumentData typeDescription
Child valueList, time period

The list or time period to return the parent of.

If the item has no parent, the function returns a blank value.

The PARENT function returns a value of the same data type as the Child value argument. However, the level within the list hierarchy is different.

Syntax example

PARENT(ITEM(Time.'Jan 21'))

This example uses the ITEM function to return the parent of January 2021. In this case it would return a result of Q1 FY21.

Calculation engine functionality differences

In Polaris, the parent of Years within the Time list is All Periods.

In the Classic Engine, All Periods has a time scale of Years, which means Years is the parent of Years.

In Polaris, the results of formulas are not coerced into different timescales. For example, if a formula returns a result with a Months timescale in a line item with the Years timescale, the formula is invalid.

In the Classic Engine, the results of formulas are coerced into different timescales. For example, if a formula returns a result with a Months timescale in a line item with the Years timescale, the Months value automatically converts to display in the Years line item.

Examples

Example usage with a list

In this example, there are two lists, Outlets and its parent list Territories. The relationship of these lists is shown in the table below as they display in General Lists.


ParentCode
Outlet A1Territory A
Outlet A2Territory A
Territory ATotal
Outlet B1Territory B
Outlet B2Territory B
Territory BTotal
Total

New Outlet 1

New Outlet 2

In the table below, three columns are line items and the rows are items from the Outlets list.

The Parent Territory line item has the list data type, and the Territories list is selected. The line item uses a formula that contains the PARENT and ITEM functions with the Outlets list. The ITEM function returns the list item for the Outlets list, and the PARENT function returns the parent of that list item, which is the territory.

The formula returns a blank result for the New Outlet 1 and New Outlet 2 list items as they do not have a parent.


Allocation %Forecast

Parent Territory

PARENT(ITEM(Outlets))

Outlet A160.0%120,000Territory A
Outlet A240.0%80,000Territory A
Territory A100.0%200,000
Outlet B175.0%150,000Territory B
Outlet B225.0%50,000Territory B
Territory B100.0%200,000
New Outlet 130.0%60,000
New Outlet 270.0%140,000

Example usage with time periods

In the table below, the rows are line items and the columns are items from the Project Dates list.

The formula used in the Parent Quarter line item uses the PARENT function to return the parent of each month in the Month Period line item. For time periods, quarters are the parent of months, so the formula returns the quarter that the month belongs to.


Start dateExpiry date
Month periodFeb 21Apr 21

Parent Quarter

PARENT(Month period)

Q1 FY21Q2 FY21
PREVIOUSVERSION

For example, you can use the PREVIOUSVERSION function to make comparisons between data from different versions of a plan.

Syntax

PREVIOUSVERSION(Expression)

Arguments

ArgumentData typeDescription
ExpressionNumber, Boolean, date, time period, list, or textThe expression to return the value from the previous version for.

The PREVIOUSVERSION function returns a result of the same format as the Expression argument.

Syntax example

PREVIOUSVERSION(Bonus)

In this example, the formula returns the value from the previous version of the Bonus line item.

Additional information

The order of versions is from top to bottom in Versions in the model settings bar.

For example, if your model's versions are ordered Budget, Actual, and Forecast, the previous version of Actual is Budget.

Constraints

The result cell that you use the PREVIOUSVERSION function in must use Versions as a dimension.

Examples

In this example, an income statement module has line items on rows, time on columns, and versions on pages. The model has three versions: Budget, Actual, and Forecast, in that order. The page selector is set to the Actual version.

The formula in the Budget Cost of Goods line item uses the PREVIOUSVERSION function to retrieve the value of Cost of Goods from the previous version. 


Jan 21Feb 21Mar 21
Cost of Goods198,734236,761200,459
Goods Sold456,987498,705460,983

Budget Cost of Goods

PREVIOUSVERSION(Cost of Goods)

200,000245,000205,000
RANK

For example, you can use the RANK function to rank different territories by sales revenue.

Syntax

RANK(Source values [, Direction] [, Equal value behavior] [, Include value] [, Ranking groups])

Arguments

ArgumentData typeDescription
Source valuesNumber, date, or time periodThe number, date, or time period to rank.
Direction (optional)Keyword

Determines the direction to rank in.

The keywords are DESCENDING and ASCENDING. There's more information in the Direction argument keywords section below.

Equal value behavior (optional)Keyword

Determines how to rank equal values.

The keywords are MINIMUM, MAXIMUM, AVERAGE, and SEQUENTIAL. There's more information in the Equal value behavior argument keywords section below.

Include value (optional)Boolean

Determines if a value is ranked.

The default value, TRUE, includes a value in the ranking.

A value of FALSE omits a value from the ranking and returns a result of NaN (Not a Number).

Ranking groups (optional)Number, Boolean, date, time period, list, or textIf provided, the source values are ranked independently for each value in the Ranking groups argument.

The RANK function returns a number-formatted result.

Direction argument keywords

KeywordDescription
DESCENDING

The default keyword if you omit the Direction argument.

When used, the RANK function assigns the highest source value rank 1, the second highest source value rank 2, and so on. 

ASCENDINGWhen used, the RANK function assigns the lowest source value rank 1, the second lowest source value rank 2, and so on.

Equal value behavior argument keywords

KeywordDescription
MINIMUM

The default keyword if you omit the Equal value behavior argument.

Gives tied values the lowest ranking of their range.

MAXIMUMGives tied values the highest ranking of their range.
AVERAGEGives tied values the average of the range of applicable rankings.
SEQUENTIALGives tied values separate rankings, in the order they occur in the underlying list.

Syntax example

RANK(Revenue, DESCENDING, MINIMUM, Include in Ranking?, Region)

This formula ranks the values of Revenue line item if the Include in Ranking? line item is TRUE. It ranks values in separate groups determined by Region.

As the formula uses the DESCENDING and MINIMUM keywords:

  • the highest values of the Revenue line item are ranked 1.
  • if there are any tied values for the Revenue line item, it assigns them both the lower rank. 

Additional information

Ranking behavior for different data types

The Source values argument for the RANK function can be a number-, date-, or time period-formatted line item, property, or expression. However, the function always returns a number-formatted result.

When the RANK function ranks values with the default DESCENDING keyword for the Direction argument, the function ranks the highest value as 1, the second highest as 2, and so on. If you use RANK with:

  • numbers, the function ranks the largest number the highest.
  • dates, the function ranks the most recent date the highest.
  • time periods, the function ranks the most recent time period the highest.

Ranking behavior across dimensions

If the module that contains the Source values argument applies to more than one other dimension (excluding Time and Versions), the ranking applies across all combinations of those dimensions. This includes values that do not currently display on your view of the data.

Consider a module with two line items, Sales and Sales Rank, which apply to Region and Product. There are 100 products sold in 50 regions.

Sales Rank = RANK(Sales)

In this example, the formula returns ranks between 1 and 5,000, ranking each combination of Product and Region across all pages.

Use RANK with the Users list

You can reference the Users list with the RANK function. However, you cannot reference specific users within the Users list as this is production data, which can change and make your formula invalid.

Constraints

Cell limit

Anaplan imposes an artificial limit of 50 million cells to prevent the ranking of large data sets that would slow down the server. If you use more than 50 million cells with the RANK function, the model rolls back and a notification displays.

The 50 million cell limit does not account for summarized values or the Time and Versions lists. This means you can use the RANK function with a line item with a Cell Count of greater than 50 million cells if there are less than 50 million non-summarized cells.

As the number of cells you use with the RANK function increases, so does the duration of the calculations.

Positive infinity, negative infinity, and NaN

Infinities and NaN are automatically excluded from the RANK function and return a result of NaN.

Calculation engine functionality differences

You cannot currently use the RANK function in Polaris.

In the Classic Engine, you can.

Excel equivalent

Examples

Example of RANK with only Source values argument


LondonBirminghamUKParisLyonFranceMunichBerlinGermanyNew YorkLos AngelesUSATotal Company
Sales11,00020,00031,00014,00014,00028,00025,0001,00026,0008,00030,00038,000123,000
RANK (Sales)63
44
28
71

This example contains an Organization list on columns, where cities are children of regions. There are two line items on rows, one for Sales, and one for the formula that contains the RANK function.

The cities are ranked by their value for the Sales line item. As the parent regions are summaries, they omit from the ranking.

As the formula only contains the Source values argument, the default keywords are used for the optional Direction and Equal value behavior arguments.

The default DESCENDING keyword is used for the Direction argument. This means the city with the largest value for the Sales line item ranks the highest.

The default MINIMUM keyword is used for the Equal value behavior argument. This means that Paris and Lyon, which both have the same value for the Sales line item, are both assigned the lower ranking. In this case, 4.

Direction argument examples


LondonBirminghamUKParisLyonFranceMunichBerlinGermanyNew YorkLos AngelesUSATotal Company
Sales11,00020,00031,00014,00014,00028,00025,0001,00026,0008,00030,00038,000123,000
RANK (Sales, DESCENDING)63
44
28
71

RANK (Sales, ASCENDING)36
44
71
28

This example shows the behavior of the two keywords for the Direction argument.

The result of the DESCENDING keyword is that the largest value for the Sales line item ranks as 1, through to the smallest value, which ranks as 8.

The result of the ASCENDING keyword is that the smallest number for the Sales line item ranks as 1, through to the largest value, which ranks as 8.

Equal value behavior argument examples


LondonBirminghamUKParisLyonFranceMunichBerlinGermanyNew YorkLos AngelesUSATotal Company
Sales11,00020,00031,00014,00014,00028,00025,0001,00026,0008,00030,00038,000123,000
RANK (Sales, DESCENDING, MINIMUM)63
44
71
28

RANK (Sales, DESCENDING, MAXIMUM)63
55
71
28

RANK (Sales, DESCENDING, AVERAGE)63
4.54.5
71
28

RANK (Sales, DESCENDING, SEQUENTIAL)63
45
71
28

This example shows the behaviors of the four keywords for the Equal value behavior argument. The values for the Sales line item for Paris and Lyon are both the same, 14,000, so they highlight the different behaviors:

  • The MINIMUM keyword assigns both Paris and Lyon the numerically lower rank, 4.
  • The MAXIMUM keyword assigns both Paris and Lyon the numerically higher rank, 5.
  • The AVERAGE keyword assigns both Paris and Lyon the average of their ranks, 4.5.
  • The SEQUENTIAL keyword assigns both Paris and Lyon ranks in the order of the underlying list, regardless of the parent hierarchy.

Include value argument examples


LondonBirminghamUKParisLyonFranceMunichBerlinGermanyNew YorkLos AngelesUSATotal Company
Sales11,00020,00031,00014,00014,00028,00025,0001,00026,0008,00030,00038,000123,000
Include in Ranking?






RANK(Sales, DESCENDING, MINIMUM, Sales > 10000 AND Sales < 24000)41
22
NaNNaN
NaNNaN

RANK(Sales, DESCENDING, MINIMUM, Sales > 10000)63
44
2NaN
NaN1

RANK(Sales, DESCENDING, MINIMUM, Include in Ranking? = TRUE)52
33
1NaN
6NaN

The Include value argument enables you to use a Boolean statement to determine whether a value is ranked. This can be a reference to a Boolean-formatted line item, or a Boolean statement entered directly into the formula.

In the first two formulas, the values to include in the ranking are determined by Boolean statements that specify they must either be:

  • Larger than 10,000 and smaller than 24,000.
  • Larger than 10,000.

The third formula contains a reference to the Include in Ranking? Boolean-formatted line item. As a result, it only ranks items in the Organization list, which the Include in Ranking? line item has a value of TRUE for.

Ranking groups argument examples


LondonBirminghamUKParisLyonFranceMunichBerlinGermanyNew YorkLos AngelesUSATotal Company
Sales11,00020,00031,00014,00014,00028,00025,0001,00026,0008,00030,00038,000123,000
Store TypeMinistoreSuperstore
MinistoreMinistore
SuperstoreWarehouse
WarehouseSuperstore

Opening Date20/3/20/2120/03/2021
20/03/202129/04/2020
29/04/202020/03/2021
25/06/202005/07/2020

RANK(Sales, DESCENDING, MINIMUM, Sales > 0, PARENT(ITEM(Organization)))21
11
12
21

RANK(Sales, DESCENDING, MINIMUM, Sales > 0, Store Type)23
11
22
11

RANK(Sales, DESCENDING, MINIMUM, Sales > 0, Opening Date)31
22
14
11

RANK(Sales, DESCENDING, MINIMUM, Sales > 0, YEAR(Opening Date))31
23
24
41

The Ranking groups argument enables you to specify criteria so that data ranks within sub-groups. The four example formulas in the table above demonstrate the following types of data used for the Ranking groups argument:

  • The parent item of each territory within the Organization list (retrieved with a combination of the PARENT and ITEM functions). This means that territories rank among their peers within each region. For example, London and Birmingham for the UK, Paris and Lyon for France, and so on.
  • A list-formatted Store Type line item that contains the type of store for each territory within the Organization list. Territories rank within groups for each store type.
  •  The Opening date for each territory. If territories share their opening date, they rank among one another.
  • The Opening date for each territory used with the YEAR function to return the year that date's in. Territories with an opening date in the same year rank among one another.

Time period- and date-formatted line item examples


Store 1Store 2Store 3Store 4Store 5Store 6Store 7Store 8
Store Closure Date01/01/2102/06/2116/07/2104/03/2114/08/21
30/04/2110/09/21
RANK(Store Closure Date, ASCENDING)25637148
Store Open Time PeriodJan 19Mar 19Jul 19Oct 19Sep 19
Jun 19Apr 19

RANK(Store Open Time Period, ASCENDING)


23687154

You can also use a date- or time period-formatted value for the Source values argument. If you do this, values rank in chronological order.

If you use the default DESCENDING keyword for the Direction argument, the most recent value ranks as 1, the second most recent value tanks as 2, and so on.

If you use the ASCENDING keyword for the Direction argument, the oldest value ranks as 1, the second oldest value ranks as 2, and so on.

A blank value for a date- or time period-formatted value is ranked as old as possible by the RANK function. As such, in this example, the RANK function ranks the blank value as 8 for the DESCENDING keyword, and 1 for the ASCENDING keyword.

RANKCUMULATE

For example, you can use the RANKCUMULATE function to cumulatively sum employees sales revenue in order of their length of service. There is an example of this in the Examples section.

Syntax

RANKCUMULATE(Cumulation values, Ranking values [, Direction] [, Include value] [, Ranking groups])

Arguments

ArgumentData typeDescription
Cumulation values (required)NumberThe number to cumulate, based on ranking criteria.
Ranking values (required)

Number, date, or time period

Can be a line item, property, or expression.

The ranking criteria to perform cumulation based on.
DirectionKeyword

Determines the direction to rank in.

The keywords are DESCENDING and ASCENDING. There's more information in the Direction argument keywords section below.

Include valueBoolean

Determines if a value is ranked.

The default value, TRUE, includes a value in the ranking.

A value of FALSE omits a value from the ranking and returns a result of 0.

Ranking groupsNumber, Boolean, date, time period, list, or textIf provided, values are ranked independently for each value of the Ranking groups argument.

The RANKCUMULATE function returns a number.

Direction argument keywords

KeywordDescription
DESCENDINGWhen used, the RANK function assigns the highest source value rank 1, the second highest source value rank 2, and so on. 
ASCENDING

The default keyword if you omit the Direction argument.

When used, the RANK function assigns the lowest source value rank 1, the second lowest source value rank 2, and so on.

Syntax example

RANKCUMULATE(Revenue, Transaction Date, DESCENDING, Eligible transaction?, Region)

Additional information

Ranking behavior for different data types

The Ranking values argument for the RANKCUMULATE function can be a number, date, or time period type line item, property, or expression. However, the function always returns a number.

When the RANKCUMULATE function ranks values with the default ASCENDING keyword for the Direction argument, the function ranks the highest value as 1, the second highest as 2, and so on. If you use RANK with:

  • Numbers, the function ranks the largest number the highest.
  • Dates, the function ranks the date further in the future the highest.
  • Time periods, the function ranks the time period furtherest in the future the highest.

Equal ranking value behavior

If two values of the Cumulation values argument share the same ranking for the Ranking values argument, ranking follows the order of any associated list items within General Lists.

Use RANKCUMULATE with the Users list

You can reference the Users list with the RANKCUMULATE function. However, you cannot reference specific users within the Users list as this is production data, which can change and make your formula invalid.

Constraints

Cell Limit

A cell limit of 50 million cells is imposed to prevent ranking of large data sets that would slow down the server. If more than 50 million cells are used with the RANKCUMULATE function, the model is rolled back and a notification displays.

The 50 million cell limit does not account for summarized values or the Time and Versions lists. This means you can use the RANKCUMULATE function with a line item with a Cell Count of greater than 50 million cells if there are fewer than 50 million nonsummarized cells.

As the number of cells you use with the RANKCUMULATE function increases, so does the duration of the calculation.

Positive Infinity, Negative Infinity, and NaN

If you use positive infinity, negative infinity, or NaN (Not a Number) for the Ranking values argument, the RANKCUMULATE function returns 0.

Cumulation Source Constraints

If your cumulation source is a large data set, the addition of numbers with a large number of decimal places can result in floating point error for the least significant digits.

If the cumulation source contains an infinity, then the result from then on until the end of the cumulation is that infinity. However, if an opposite infinity follows it, the result becomes NaN (Not a Number).

Calculation engine functionality differences

You cannot currently use the RANKCUMULATE function in Polaris.

In the Classic Engine, you can.

Excel equivalent

Examples

General examples

In this example, a module that contains the Salespersons list is on columns, and a number of line items is on rows.

The example uses RANKCUMULATE to cumulatively sum sales in the order of each salesperson's length of service. Further iterative formulas use the Include value and Ranking groups arguments to:

  • Determine which salesperson's sales to include in the cumulative ranking.
  • Further break down the cumulative ranking by region.

Two line items use the RANK function to help you identify the order that RANKCUMULATE cumulates values in.


BenGrahamRashidLauraRitaDavidMasakiKieranAlisaKarenMartinaOswald
RegionNorthNorthNorthSouthSouthSouthEastEastEastWestWestWest
Sales258,796235,884190,750228,315171,494234,276230,213222,777201,855271,162267,401209,368
Years of service6981211913145151114

Rank by years of service

RANK(Years of Service, DESCENDING)

118105684212162

Rank by years of service within region

RANK(Years of Service, DESCENDING, MINIMUM, TRUE, Region)

312123213132
Include in cumulation?

Cumulated sales by longest tenure

RANKCUMULATE(Sales, Years of Service, DESCENDING)

2,520,4361,836,6142,261,6401,161,8351,333,3292,070,890933,520493,9392,722,291271,1621,600,730703,307

Cumulated sales by tenure for selected employees

RANKCUMULATE(Sales, Years of Service, DESCENDING, Include in cumulation?)

685,430235,884426,634228,315399,8090452,990222,777654,845271,1620480,530

Cumulative sales by tenure for each region

RANKCUMULATE(Sales, Years of Service, DESCENDING, TRUE, Region)

685,430235,884426,634228,315399,809634,085452,990222,777654,845271,162747,931480,530
VALUE

For example, you can use VALUE in combination with other functions to remove currency symbols or codes from data and convert the values to numbers.

Syntax

VALUE(Value to convert)

Arguments

ArgumentData typeDescription
Value to convertTextThe text value to convert to a number value.

The VALUE function returns a numeric result.

Syntax example

VALUE(Revenue as text)

This example uses the  The VALUE function to convert the Revenue as text line item to a number value.

Additional information

Character compatibility

The VALUE function works only with text values that represent numbers. The value to be converted can contain non-numeric characters if they are:

  • Special numbers such as Infinity, -Infinity, or NaN.
  • A hexadecimal number.
  • Numbers that use scientific notation.
  • Negative numbers that contain the - symbol.

If the Value to convert argument contains any other characters, such as a comma, %, $, or £, the VALUE function returns a value of NaN (not a number).

Hexadecimal values

You can use the VALUE function to return a hexadecimal value as a number. For example, the formula VALUE(“0x11.11p0") returns a number value of 17.07.

Special numbers

The VALUE function also works with special numbers such as Infinity, -Infinity, or NaN.

Calculation engine functionality differences

In Polaris, you cannot use the VALUE function with non-decimal representations of numbers (for example, “0x11.11p0”).

In the Classic Engine, you can use non-decimal representations of numbers.

Excel equivalent

Examples

Basic example of VALUE function

This example has a list on columns that contains items with descriptive names for the values they contain. On rows, there are two line items:

  • Text value, which contains the text values for the Value to convert argument.
  • Number value, which uses the VALUE function to convert these text values to number values.
    This formula also highlights how the VALUE function interacts with numbers that contain non-numeric characters.

PositiveNegativeDollarSterlingComma separatorsScientific notationHexadecimal
Text value134486.12-134486.12$134486.12£134486.12134,486.123E80x11.11p0

Converted number-formatted values

VALUE('Text-formatted values')

123,456,789-123,456,789NaNNaNNaN300,000,00017.07

Use VALUE with the MID function

Some functions, such as MID, return a text-formatted result. If these results are numeric, you can use the VALUE function to convert them to number-formatted.

For example, you can use the MID function to extract all characters after the first character. This enables you to remove currency symbols from numbers stored as text. The result can then be used with the VALUE function.

In this example the Revenue as text line item is text, and contains a value for several different currencies. The Revenue as number line item uses the MID and VALUE functions to convert the text that contains a number to numbers.



US DollarsBritish PoundEuroYen
Revenue as text$2348765.38£1674500.96€1945632.29¥257478438.94

Revenue as number

VALUE(MID(Revenue, 2))

2,348,765.381,674,500.961,945,632.29257,478,438.94

Disclaimer

We update Anapedia regularly to provide the most up-to-date instructions.