1. Calculation functions
  2. All Functions
  3. Miscellaneous Functions
  4. ITEM

Returns the list item of a list-formatted or time period-formatted line item.

This is especially useful for conditionals, where you want to make the result dependent upon which list item of a hierarchy you are on.

Syntax

ITEM(x) 

where:

x: list hierarchy or time period hierarchy that exists in the model.

Format

Input Format Output Format

x: List, time period

Matches the data format used by argument x

Arguments

The function uses the following arguments:

  • x: list-formatted or time period-formatted line item

Constraints

The function has the following constraints:

  • When using the ITEM(x) function, where x is a child list in a composite hierarchy, a summary formula cannot be used. For summaries, the ITEM() function tries to retrieve the value from the parent list, and only the child list is in Blueprint > Applies To column.

Excel equivalent

  • No Excel equivalent

Back to Top

Example

If a line item applies to Organization and Products then:

ITEM(Organization) 

is the relevant item in the organizational hierarchy

ITEM(Products) 

is the relevant item in the products list

This may be used where a specific calculation is required for certain items:

IF ITEM(Products) = Products.Widget  
THEN <... calculation for Widget ...>
ELSE <... calculation for other products ...>

Or even:

IF ITEM(Products) = Products.Widget AND ITEM(Organization) = Organization.US South
THEN <... calculation for Widgets in US South ...>
ELSE <... calculation for all other products/regions ...>

Syntax examples for conditional contexts:

IF ITEM(Products) = Products.Vegetables THEN Sales * 0.5 ELSE Sales * 0.6          
		IF ITEM(Time) < TIME.'Apr 11' THEN 1 ELSE 2

List Example

In this example, a simple two-dimensional Company Status module shows line items on rows with the Organization list on columns. The Company Class Status is a list-formatted line item using the Company Class list and uses a conditional formula that embeds the ITEM function to assign a company status grade from the Company Class list, depending on which company in the Organization list you are on in the result cell. Company 1 and Company 5 are assigned Class A status and the rest of the companies are assigned a Class B status:

IF ITEM(Organization)=Organization.'Company 1' THEN Company Class.Class A ELSE IF ITEM(Organization) = Organization.'Company 5' THEN Company Class.Class A ELSE Company Class.Class B 

A similar example for the same module shows a number-formatted line item called Company Number Status that uses a conditional formula that embeds the ITEM function to assign a number ranking for company status depending on which company you are on:

IF ITEM(Organization) = Organization.'Company 1' THEN 1 ELSE IF ITEM(Organization) = Organization.'Company 5' THEN 1 ELSE 2

Time Example

In this example, a simple two-dimensional Company Launch module shows line items on rows and has a time dimension. The Company Launch Period line item is list-formatted to use the Launch Periods list and uses a conditional formula that embeds the ITEM function to assign a launch period from the Launch Periods list, depending on what month time period you are on in the result cell with reference to the module's time dimension. The company launch is planned for April 2014 - until that date is pre-launch, post-launch after that:

IF ITEM(Time) < TIME.'Apr 14' THEN LAUNCH Periods.Pre Launch ELSE LAUNCH Periods.Post Launch
		

A similar example for the same module shows a number-formatted line item called Company Launch Stage that uses a conditional formula that embeds the ITEM function to assign a launch stage number for the period depending on which time period the result cell is in with reference to the module's time dimension:

Back to Top

Similar functions