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.
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)
Arguments
Argument | Data Type | Description |
List | List or Time | The name of the list, or a reference to a time period, to return the item from. |
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")
to return the text match of Bolts from the list Hardware.
This is only applicable for List. You can't use this two-argument version for Time, such as ITEM(Time, "Jan 22")
, as FINDITEM is not supported on Time.
Calculation engine functionality differences
- In Polaris, you can't use the ITEM function for line items that have the Formula summary method. In the Classic Engine, you can.
- In Polaris, you can't use the ITEM function with a two-argument variant of Time, for example ITEM(Time, "Feb 10"). In the Classic Engine, you can.
Syntax example
ITEM(Products)
Here, the ITEM function returns the applicable value for the Products list, which is a dimension in the module.
Constraints
- You can't use the ITEM function with the Formula summary method. This is because the parent list isn't 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.
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 |