Returns a Boolean indicating if a value in the first parameter is the first occurrence of that value within the specified dimension of the source (second parameter).
- x: Value to search for
- y: Dimension of x to search along
|Input Format||Output Format|
x: Number, Boolean, date, list, text
y: Name of List
The function uses the following arguments:
- x: Number, Boolean, date, List item, text
- y: Name of list
The function has the following constraints:
- There is a 10 million item limit imposed on this function. If you deploy this function against a data set containing more than 1 million items, any change in source values could result in calculations that take a few seconds.
If the list used in the function rolls up into a parent list, changing the order of the parent list may alter your results. In this case, ISFIRSTOCCURRENCE works off the order of the hierarchical lists itself and not the order as it appears in the module where it is ordered hierarchically with respect to the parent list.
- No Excel equivalent
Here's a module showing how this function can be used. The function identifies the first occurrence of multiple uses of a list value in the module:
ISFIRSTOCCURRENCE(Which Product, Contracts)
The formula is entered in Blueprint. Note the Boolean format:
If you create a list that uses a second list as a parent, the ISFIRSTOCCURRENCE function will base the result on the order of the child list, but the module will arrange the display of results based on the hierarchy introduced by parent list.
Regions contains the values Asia, Americas, Europe, and Australia.
Locations (that lists the Countries) uses Regions as its Parent List.
Type contains the type of Business at each location, Admin, Distribution, Manufacturing, All.
IFO-Check module uses Locations as the rows.
The line items Value and IsFirst are dimensioned as columns.
Value is number-formatted
IsFirst is Boolean with the following formula :
In this case, the IsFirst column appears to displays check mark indicating the first occurrence of a value in the same way as the example above.
However, if the order of Regions is altered, the results appear incorrect; this is because the function references the order in the child (Locations) list, but the results are ordered according to the parent Regions list.
Instead of indicating the first occurrence of a value within the module as before, the IsFirst column indicates the first occurrence of each value in the module Value column, when it appears on the child Locations list, but ordered by the parent Regions list.
This can be seen if all the parent values are removed from the Locations list.
With the parent values removed, it is clear how the function obtains the result.