The ISFIRSTOCCURRENCE function returns a Boolean value of TRUE for the first occurrence of a value in a list dimension.
For example, you can use ISFIRSTOCCURRENCE to prevent duplicates when creating lists from data.
Syntax
ISFIRSTOCCURRENCE(Values to compare, List dimension to search)
Arguments
| Argument | Data type | Description |
| Values to compare | Number, Boolean, date, time period, list, or text | The values to search for the first occurrences of. |
| List dimension to search | List | The list to search along for the first occurrences of the Value to find argument. Must be a dimension of the target line item. |
The ISFIRSTOCCURRENCE function returns a Boolean result.
Calculation engine functionality differences
In Polaris, you can use the ISFIRSTOCCURRENCE function with the Time list. In the Classic Engine, you cannot.
Additionally, in Polaris, the ISFIRSTOCCURRENCE function does not have a 50 million cell limit as it does in the Classic Engine.
In Polaris, the dimension argument must match one of the dimensions of the target line item. For example, you cannot use a subset of a list as a dimension argument. In the Classic Engine, this is not the case.
Note: Performance of ISFIRSTOCCURRENCE is known to be poor in Polaris for models with high dimensionality. We strongly advise you to avoid ISFIRSTOCCURRENCE in Polaris.
Syntax example
ISFIRSTOCCURRENCE(Product, Contracts)
Additional information
Use ISFIRSTOCCURRENCE with the Users list
You can reference the Users list with the ISFIRSTOCCURRENCE function. However, you cannot reference specific users within the Users list as this is production data, which can change and make your formula invalid.
Hierarchical lists
The ISFIRSTOCCURRENCE function references the order of the leaf list as seen in General Lists, not the parent list. This means that the first occurrence of an item may not be the first that displays in a list within a module. You can change the order of lists with the Order List action.
Constraints
Cell limit
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.
Examples
General example
In this example, a Contracts list displays on rows, and line items on columns. The line items contain information about each contract, and the Is first occurrence? line item uses the ISFIRSTOCCURRENCE function to identify the first contract for each product.
| Product | Transaction amount | Is first occurrence?
| |
| Contract 1 | Peaches | 1,216.40 | |
| Contract 2 | Peaches | 864.20 | |
| Contract 3 | Peaches | 2.165.60 | |
| Contract 4 | Bananas | 3,485.00 | |
| Contract 5 | Bananas | 1.692.10 | |
| Contract 6 | Peaches | 1,451.20 |
Example with expression for Values to compare argument
The Values to compare argument can be any valid expression. In this example, a Transactions list displays on rows, and line items on columns.
Each transaction has a fee subtracted from it. The ISFIRSTOCCURRENCE function is used to identify the first occurrence of each value after subtraction.
| Transaction value | Transaction fee | First occurrence of transaction value
| |
| Transaction 1 | 1,000 | 25 | |
| Transaction 2 | 1,100 | 125 | |
| Transaction 3 | 500 | 10 | |
| Transaction 4 | 550 | 60 | |
| Transaction 5 | 1,050 | 75 |
