Last_value and first_value functions

The basic syntax of the last_value function is: last_value(operation(measure or attribute),query_groups(),{column to order by}). For example: last_value(sum(balance),query_groups(),{transaction date}).

The first argument, "sum(balance)" identifies the aggregate type and the measure column. The second argument, "query_groups()", partitions the data by column list; Use query_groups so that all columns in the search bar are included. The final argument, "{transaction date}", orders the results by that column.

Note that last_value and first_value functions support the query_groups() + () and query groups() - () arguments like group aggregate functions do. You can enter multiple columns in the third argument. For example, query_groups()+{partner_id} would partition by all grouping columns in the search and ensure that partner_id is always included. query_groups()-{unit price, sales price} partitions by all grouping columns in the search except unit price and sales price.

The inner aggregation within the first argument is specified within the last_value or first_value function, for example, last_value(sum(balance),…​). The aggregation type, in this case, sum, determines how values are aggregated within partitions. If a non-aggregate value is returned, you can use a min or max aggregation.

You can include an outer aggregate function wrapping around the last_value/first_value function. If you don’t supply an outer aggregate, the aggregation defaults to SUM for numeric columns and MAX for non-numeric columns. You can use simple aggregates like sum, min, max, average, or advanced aggregates like group_average, moving_sum, and cumulative_sum.

If you do not specify an outer aggregate function in your formula, you can also add it from the search bar by entering "max", "min", "sum", etc..

Consider the following example formulas:

In the following use case, consider a data set that receives a snapshot of products each day. There are no missing dates in the time series, all attribute (date/product/store) combinations are represented, and zero values are explicitly recorded rather than being null or missing. For this example, we will use the following closing balance formula:

The following table shows the sample data from January 1st to the present day, March 15th.

To find the current inventory balance, you can search for fxClosingBalance. The formula results in 25. The result is the sum of all balances on the last recorded date, March 15th.

If you search for fxClosingBalance by product, the following table results:

The result is the same, 25.

If you search for fxClosingBalance by product monthly, you receive the final balance for each month, 30 for January, 25 for February, and 25 for March. The following table results:

Note that the date for March is the 15th. The value does not have to be the absolute last date of the period.

If you search for fxClosingBalance by product yearly, you receive a single value, 25, because the data set only contains one year’s data.

The following table results:

The final date is March 15th, so the formula returns the product balances for that date, 25.

For these examples, we will be using the following formula to find the opening balance of a data set:

For this example, we will be examining a data set with incomplete data. In the following table, a new product, oranges, was introduced on March 15th. There are no entries for oranges in the previous months, so not all combinations of date and products have been defined.

To find the opening inventory balance, you can search for fxOpeningBalance, for which you will receive a value of 20, the sum of all balances on January 1st.

If you search for fxOpeningBalance by product, however, you will receive an answer of 30. Reference the following table:

Although the first date in the table is January 1st, the value for oranges on March 15th is included in the result, as it is the first value for this product.

In order to fix this dataset to prevent erroneous values in the first_value function, you need to include zero value balances for oranges for the preceding time periods.

Now, if you search for fxOpeningBalance by product, you receive a value of 20. The zero value balance for oranges on January 1st is included.

In the following scenario, the snapshot data fails the principle that all dates are represented. Data such as store inventory may arrive on different dates. The business considers the last store snapshot to be valid until a more recent one arrives.

The following is a subset of the snapshot table. Note that the most recent transaction date is March 15th. However, the snapshot has not been received for all stores on this date.

To find out the closing balance for this data, consider the following formula:

If you search for fxClosingBalance, you get a result of 40, the sum of all balances on the last date, March 15th. This does not include the values for Kirkwood, since the last date for that data is March 14th.

If you then search for fxClosingBalance by store, you get a result of 55. The value for Kirkwood is included, as the search breaks the result down by store.

In order to ensure the total values remain consistent from an aggregate to detail, the formula must be augmented to ensure that Store is always included as a partitioning column.

Use the following formula:

Now, when you search for fxClosingBalance or fxClosingBalance by product, you get a result of 55 values for both searches.