Use the ISFIRSTOCCURENCE function to return a TRUE Boolean value for the first occurrence of each value in a list-formatted dimension.
|Value to search for||Number, Boolean, date, time period, list, or text||The value to search for the first occurrence of.|
|List dimension to search||List||The list to search within for the first occurrence of a value. Must be a dimension in a module.|
The ISFIRSTOCCURRENCE function returns a Boolean-formatted result.
The Value to search for argument can be a number-, Boolean-, date-, time period-, list-, or text-formatted line item.
The Dimension to search argument must be a list used as a dimension in a module. It cannot be a list-formatted line item.
The ISFIRSTOCCURRENCE function has the following constraints:
An artificial limit is imposed to prevent the searching of large data sets that would slow down the server. This limit is set at 50 million cells. If more than 50 million cells are used with the ISFIRSTOCCURRENCE 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 ISFIRSTOCCURRENCE function with a line item with a Cell Count of greater than 50 million cells if there are less than 50 million nonsummarized cells.
As the number of cells you use with the ISFIRSTOCCURRENCE function increases, so does the duration of the calculation.
The ISFIRSTOCCURRENCE function references hierarchical lists in a model, as seen in General Lists. This means the results of the ISFIRSTOCCURRENCE function may not match the the order of items in the module. You can change the order of lists with the Order List action.
- No Excel equivalent
In this module, ISFIRSTOCCURRENCE identifies the first occurrence for each list value:
ISFIRSTOCCURRENCE(Which Product, Contracts)
Below is the formula in Blueprint. Note that the result line item for the line item named ISFIRSTOCCURRENCE is Boolean-formatted:
How hierarchical lists interact with ISFIRSTOCCURRENCE
If you create a list that uses a second list as a parent, the ISFIRSTOCCURRENCE function bases the result on the order of the child list. However, the module orders the results based on the hierarchy of the parent list. This example shows how a child list named named Type interacts with its parent lists Locations and Regions as part of a hierarchy.
Regions contains the values Asia, Americas, Europe, and Australia.
Locations contains a list of countries, and uses Regions as its Parent list.
Type contains the type of business performed at each location: Admin, Distribution, Manufacturing, and All.
The IFO-Check module uses Locations as the rows.
The line items Value and IsFirst are dimensioned as columns. Value is list-formatted. IsFirst is Boolean-formatted, and contains this following formula :
As a result, checkmarks display in the IsFirst column. These indicate the first occurrence of each value in the Value column.
If you alter the order of items in the parent Regions list, the results appear to be incorrect. This is because the function references the order in the child Locations list, but the parent Regions list determines the order of the results in the module. This means that the first occurrence of a value might not be top one in the IsFirst column.
If all parents are removed from the Locations list, the list items display in the order below. This is the order that the ISFIRSTOCCURRENCE function searches the list items. The search is performed in this order, regardless of the order of the parent Regions list, or the list items within the module.