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
Argument | Data type | Description |
Item | List or time period | The 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
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 Johnson | Barbara Jones | Hannah Smith | |
Length of Service (years) | 6 | 8 | 2 |
Commission Rate % | 3.25% | 3.5% | 2% |
Personnel Code
| SP001 | SP002 | SP003 |
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 21 | Feb 21 | Mar 21 | Apr 21 | May 21 | Jun 21 | |
Sales Staff | John Johnson | John Johnson | John Johnson | Hannah Smith | Hannah Smith | Hannah Smith |
Sales Commission | 20,366 | 10,300 | 13,422 | 20,002 | 1,999 | 15,698 |
Personnel Code
| SP001 | SP001 | SP001 | SP003 | SP003 | SP003 |
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 year | 1200000 |
% increase | 5% |
Sales | 1260000 |
Margin % | 40% |
Cost of Sales | 756000 |
Gross Margin | 2016000 |
Headcount | 10 |
Cost per employee | |
Staff Costs | 360000 |
Rent & Rates | 12000 |
Utilities | 12000 |
Marketing | 12000 |
IT costs | 12000 |
Total Overheads | 408000 |
Operating Profit | 1608000 |
Revenue module:
FY15 | |
Assets Sold | 720000 |
Buildings | 120000 |
Fixtures | 120000 |
Software | 120000 |
Patents | 120000 |
Assets Purchased | 480000 |
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 | |
Sales | 1260000 |
Cost of Sales | 756000 |
Gross Margin | 2016000 |
Staff Costs | 360000 |
Rent & Rates | 12000 |
Utilities | 12000 |
Marketing | 12000 |
IT costs | 12000 |
Total Overheads | 408000 |
Operating Profit | 1608000 |
Assets Sold | 720000 |
Buildings | 120000 |
Fixtures | 120000 |
Software | 120000 |
Patents | 120000 |
Assets Purchased | 480000 |
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.
For example, you can use the CURRENTVERSION function to compare data between versions.
Syntax
CURRENTVERSION(Line item)
Arguments
Argument | Data type | Description |
Line item | Number, Boolean, date, time period, list, or text | The 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.
Chocolates | Sours | Taffy | Fudge | |
Cost of Goods | 190,000 | 150,000 | 250,000 | 180,000 |
Cost of Goods (Actual)
| 198,000 | 140,433 | 250,670 | 203,005 |
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
Argument | Data type | Description |
List or Time | List 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 find | Text | The 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
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.
Parent | Code | Fruit subset | Vegetable subset | |
Apples | Fruit | APP001 | ||
Peaches | Fruit | PEA002 | ||
Bananas | Fruit | BAN003 | ||
Fruit | Total Products | |||
Carrots | Vegetables | CAR004 | ||
Cucumbers | Vegetables | CUC005 | ||
Lettuce | Vegetables | LET006 | ||
Vegetables | Total 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 22 | Feb 22 | Mar 22 | Apr 22 | May 22 | Jun 22 | |
Top-selling product | Apples | Bananas | Cucumbers | Cucumbers | Peaches | Lettuce |
Fruit product top seller?
| Apples | Bananas | Peaches | |||
Vegetable product top seller?
| Cucumbers | Cucumbers | 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 22 | Feb 22 | Mar 22 | Apr 22 | May 22 | Jun 22 | |
Code of top-selling product | APP001 | BAN003 | CUC005 | CUC005 | PEA002 | LET006 |
Fruit product top seller?
| Apples | Bananas | Peaches | |||
Vegetable product top seller?
| Cucumbers | Cucumbers | Lettuce |
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
Argument | Data Type | Description |
List or Time | List or Time dimension | The 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 becomeFINDITEM(Hardware, "Bolts")
. - The formula
ITEM(Time, "Jan 22")
updates to becomeFINDITEM(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 22 | Feb 22 | Mar 22 | |
Product 1 | Jan 22 | Feb 22 | Mar 22 |
Product 2 | Jan 22 | Feb 22 | Mar 22 |
Product 3 | Jan 22 | Feb 22 | Mar 22 |
Product 4 | Jan 22 | Feb 22 | Mar 22 |
The table below displays the result of the ITEM(Products)
formula.
Jan 22 | Feb 22 | Mar 22 | |
Product 1 | Product 1 | Product 1 | Product 1 |
Product 2 | Product 2 | Product 2 | Product 2 |
Product 3 | Product 3 | Product 3 | Product 3 |
Product 4 | Product 4 | Product 4 | Product 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 01 | Company 02 | Company 03 | Company 04 | Company 05 | Company 06 | Company 07 | Company 08 | |
Class | Class A | Class B | Class B | Class B | Class A | Class B | Class B | Class 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 21 | Feb 21 | Mar 21 | Apr 21 | May 21 | Jun 21 | Jul 21 | Aug 21 | Sep 21 | Oct 21 | Nov 21 | Dec 21 | |
Product Release Status | Before Release | Before Release | Before Release | After Release | After Release | After Release | After Release | After Release | After Release | After Release | After Release | After Release |
For example, you can use the NEXTVERSION function to make comparisons between data from different versions of a plan.
Syntax
NEXTVERSION(Expression)
Arguments
Argument | Data type | Description |
Expression | Number, Boolean, date, time period, list, or text | The 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 21 | Feb 21 | Mar 21 | |
Revenue Costs | 12,457 | 13,987 | 13,483 |
Salary Costs | 27,950 | 28,000 | 22,765 |
Forecast Revenue Costs
| 15,000 | 15,500 | 16,000 |
For example, you can use PARENT to see which quarter a month belongs to.
Syntax
PARENT(Child value)
Arguments
Argument | Data type | Description |
Child value | List, 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.
Parent | Code | |
Outlet A1 | Territory A | |
Outlet A2 | Territory A | |
Territory A | Total | |
Outlet B1 | Territory B | |
Outlet B2 | Territory B | |
Territory B | Total | |
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
| |
Outlet A1 | 60.0% | 120,000 | Territory A |
Outlet A2 | 40.0% | 80,000 | Territory A |
Territory A | 100.0% | 200,000 | |
Outlet B1 | 75.0% | 150,000 | Territory B |
Outlet B2 | 25.0% | 50,000 | Territory B |
Territory B | 100.0% | 200,000 | |
New Outlet 1 | 30.0% | 60,000 | |
New Outlet 2 | 70.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 date | Expiry date | |
Month period | Feb 21 | Apr 21 |
Parent Quarter
| Q1 FY21 | Q2 FY21 |
For example, you can use the PREVIOUSVERSION function to make comparisons between data from different versions of a plan.
Syntax
PREVIOUSVERSION(Expression)
Arguments
Argument | Data type | Description |
Expression | Number, Boolean, date, time period, list, or text | The 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 21 | Feb 21 | Mar 21 | |
Cost of Goods | 198,734 | 236,761 | 200,459 |
Goods Sold | 456,987 | 498,705 | 460,983 |
Budget Cost of Goods
| 200,000 | 245,000 | 205,000 |
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
Argument | Data type | Description |
Source values | Number, date, or time period | The 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 text | If 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
Keyword | Description |
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. |
ASCENDING | When 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
Keyword | Description |
MINIMUM | The default keyword if you omit the Equal value behavior argument. Gives tied values the lowest ranking of their range. |
MAXIMUM | Gives tied values the highest ranking of their range. |
AVERAGE | Gives tied values the average of the range of applicable rankings. |
SEQUENTIAL | Gives 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
London | Birmingham | UK | Paris | Lyon | France | Munich | Berlin | Germany | New York | Los Angeles | USA | Total Company | |
Sales | 11,000 | 20,000 | 31,000 | 14,000 | 14,000 | 28,000 | 25,000 | 1,000 | 26,000 | 8,000 | 30,000 | 38,000 | 123,000 |
RANK (Sales) | 6 | 3 | 4 | 4 | 2 | 8 | 7 | 1 |
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
London | Birmingham | UK | Paris | Lyon | France | Munich | Berlin | Germany | New York | Los Angeles | USA | Total Company | |
Sales | 11,000 | 20,000 | 31,000 | 14,000 | 14,000 | 28,000 | 25,000 | 1,000 | 26,000 | 8,000 | 30,000 | 38,000 | 123,000 |
RANK (Sales, DESCENDING) | 6 | 3 | 4 | 4 | 2 | 8 | 7 | 1 | |||||
RANK (Sales, ASCENDING) | 3 | 6 | 4 | 4 | 7 | 1 | 2 | 8 |
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
London | Birmingham | UK | Paris | Lyon | France | Munich | Berlin | Germany | New York | Los Angeles | USA | Total Company | |
Sales | 11,000 | 20,000 | 31,000 | 14,000 | 14,000 | 28,000 | 25,000 | 1,000 | 26,000 | 8,000 | 30,000 | 38,000 | 123,000 |
RANK (Sales, DESCENDING, MINIMUM) | 6 | 3 | 4 | 4 | 7 | 1 | 2 | 8 | |||||
RANK (Sales, DESCENDING, MAXIMUM) | 6 | 3 | 5 | 5 | 7 | 1 | 2 | 8 | |||||
RANK (Sales, DESCENDING, AVERAGE) | 6 | 3 | 4.5 | 4.5 | 7 | 1 | 2 | 8 | |||||
RANK (Sales, DESCENDING, SEQUENTIAL) | 6 | 3 | 4 | 5 | 7 | 1 | 2 | 8 |
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
London | Birmingham | UK | Paris | Lyon | France | Munich | Berlin | Germany | New York | Los Angeles | USA | Total Company | |
Sales | 11,000 | 20,000 | 31,000 | 14,000 | 14,000 | 28,000 | 25,000 | 1,000 | 26,000 | 8,000 | 30,000 | 38,000 | 123,000 |
Include in Ranking? | |||||||||||||
RANK(Sales, DESCENDING, MINIMUM, Sales > 10000 AND Sales < 24000) | 4 | 1 | 2 | 2 | NaN | NaN | NaN | NaN | |||||
RANK(Sales, DESCENDING, MINIMUM, Sales > 10000) | 6 | 3 | 4 | 4 | 2 | NaN | NaN | 1 | |||||
RANK(Sales, DESCENDING, MINIMUM, Include in Ranking? = TRUE) | 5 | 2 | 3 | 3 | 1 | NaN | 6 | NaN |
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
London | Birmingham | UK | Paris | Lyon | France | Munich | Berlin | Germany | New York | Los Angeles | USA | Total Company | |
Sales | 11,000 | 20,000 | 31,000 | 14,000 | 14,000 | 28,000 | 25,000 | 1,000 | 26,000 | 8,000 | 30,000 | 38,000 | 123,000 |
Store Type | Ministore | Superstore | Ministore | Ministore | Superstore | Warehouse | Warehouse | Superstore | |||||
Opening Date | 20/3/20/21 | 20/03/2021 | 20/03/2021 | 29/04/2020 | 29/04/2020 | 20/03/2021 | 25/06/2020 | 05/07/2020 | |||||
RANK(Sales, DESCENDING, MINIMUM, Sales > 0, PARENT(ITEM(Organization))) | 2 | 1 | 1 | 1 | 1 | 2 | 2 | 1 | |||||
RANK(Sales, DESCENDING, MINIMUM, Sales > 0, Store Type) | 2 | 3 | 1 | 1 | 2 | 2 | 1 | 1 | |||||
RANK(Sales, DESCENDING, MINIMUM, Sales > 0, Opening Date) | 3 | 1 | 2 | 2 | 1 | 4 | 1 | 1 | |||||
RANK(Sales, DESCENDING, MINIMUM, Sales > 0, YEAR(Opening Date)) | 3 | 1 | 2 | 3 | 2 | 4 | 4 | 1 |
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 1 | Store 2 | Store 3 | Store 4 | Store 5 | Store 6 | Store 7 | Store 8 | |
Store Closure Date | 01/01/21 | 02/06/21 | 16/07/21 | 04/03/21 | 14/08/21 | 30/04/21 | 10/09/21 | |
RANK(Store Closure Date, ASCENDING) | 2 | 5 | 6 | 3 | 7 | 1 | 4 | 8 |
Store Open Time Period | Jan 19 | Mar 19 | Jul 19 | Oct 19 | Sep 19 | Jun 19 | Apr 19 | |
| 2 | 3 | 6 | 8 | 7 | 1 | 5 | 4 |
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.
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
Argument | Data type | Description |
Cumulation values (required) | Number | The 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. |
Direction | Keyword | Determines the direction to rank in. The keywords are DESCENDING and ASCENDING. There's more information in the Direction argument keywords section below. |
Include value | 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 0. |
Ranking groups | Number, Boolean, date, time period, list, or text | If provided, values are ranked independently for each value of the Ranking groups argument. |
The RANKCUMULATE function returns a number.
Direction argument keywords
Keyword | Description |
DESCENDING | When 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.
Ben | Graham | Rashid | Laura | Rita | David | Masaki | Kieran | Alisa | Karen | Martina | Oswald | |
Region | North | North | North | South | South | South | East | East | East | West | West | West |
Sales | 258,796 | 235,884 | 190,750 | 228,315 | 171,494 | 234,276 | 230,213 | 222,777 | 201,855 | 271,162 | 267,401 | 209,368 |
Years of service | 6 | 9 | 8 | 12 | 11 | 9 | 13 | 14 | 5 | 15 | 11 | 14 |
Rank by years of service
| 11 | 8 | 10 | 5 | 6 | 8 | 4 | 2 | 12 | 1 | 6 | 2 |
Rank by years of service within region
| 3 | 1 | 2 | 1 | 2 | 3 | 2 | 1 | 3 | 1 | 3 | 2 |
Include in cumulation? | ||||||||||||
Cumulated sales by longest tenure
| 2,520,436 | 1,836,614 | 2,261,640 | 1,161,835 | 1,333,329 | 2,070,890 | 933,520 | 493,939 | 2,722,291 | 271,162 | 1,600,730 | 703,307 |
Cumulated sales by tenure for selected employees
| 685,430 | 235,884 | 426,634 | 228,315 | 399,809 | 0 | 452,990 | 222,777 | 654,845 | 271,162 | 0 | 480,530 |
Cumulative sales by tenure for each region RANKCUMULATE | 685,430 | 235,884 | 426,634 | 228,315 | 399,809 | 634,085 | 452,990 | 222,777 | 654,845 | 271,162 | 747,931 | 480,530 |
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
Argument | Data type | Description |
Value to convert | Text | The 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.
Positive | Negative | Dollar | Sterling | Comma separators | Scientific notation | Hexadecimal | |
Text value | 134486.12 | -134486.12 | $134486.12 | £134486.12 | 134,486.12 | 3E8 | 0x11.11p0 |
Converted number-formatted values
| 123,456,789 | -123,456,789 | NaN | NaN | NaN | 300,000,000 | 17.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 Dollars | British Pound | Euro | Yen | |
Revenue as text | $2348765.38 | £1674500.96 | €1945632.29 | ¥257478438.94 |
Revenue as number
| 2,348,765.38 | 1,674,500.96 | 1,945,632.29 | 257,478,438.94 |