FirstInRange

Returns the first value in a range of values. FirstInRange can be used to examine data such as inventory at the beginning of each month.

Calculations can be restarted based on attributes set in the function parameters. This restart capability differentiates FirstInRange from First. While First is a group-value function and takes both facts and metrics as argument input, FirstInRange is an OLAP function and takes only metrics as argument input.

Syntax

FirstInRange <BreakBy, SortBy> (Argument)

Where:

• BreakBy is the attribute indicating where the calculation restarts.
• SortBy is the attribute or metric by which the data is sorted.
• Argument is a metric representing a list of numbers.

Expression

Where:

• k = row number
• y1= first non-NULL value of the metric after applying the BreakBy and SortBy parameters

Example

This example shows the results of using the FirstInRange function. The report includes the attributes Customer Region and Customer State and the metrics Profit and FirstInRange. A report filter limits the regions displayed to Northwest, Southeast, and Southwest. The FirstInRange metric is defined as follows:

FirstInRange<BreakBy={[Customer Region]}, SortBy= ([Customer State]) >(Profit)

In the following report, FirstInRange returns the first profit value in the list of Customer States for each Customer Region.