Semi-additive measures with first_value and last_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.

You can include an outer aggregate function wrapping around the last_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:

For the following examples, consider a schema where the Stock Balances fact table is connected to the Date dimension table on the date column, and connected to the Product dimension table on the sku column.

Note that for the Stock Balances fact table, the grain is date and product. For each date, there is a row that defines what the balance for the product is at the sku level. The final transaction date is December 30th.

This is a semi-additive fact table; we can add up the balances for the product column, but we can’t add them up over all dates. The true balance for a select date is the balance recorded that day, it is not the sum of balances for the dates leading up to it.

To calculate the last value of the stock balance for a date, create the stock balance formula and add it to your search:

stock balance = last_value(sum(balance),query_groups(),{date})

The formula returns a sum of all the balance values for the last date in the dataset. Here, the date column comes from the date dimension table. The final date in the date dimension table is December 31st, but the last date for which there is a balance value is December 30th. The formula returns the balance for December 30th: 13.

If you search for stock balance yearly, the formula sums the balance values for the last date in each year. If your dataset contained multiple years, it would sum the balance for each year. Since this dataset contains only one year, the formula returns the balance for December 30th: 13.

If you search for stock balance group january, the formula sums the balance for the last date in the dataset, filtered for January, and separated by group. The formula returns the following table:

If you search for stock balance weekly january group, the formula shows the sum of all balance values for the last week for each group, filtered by january, and separated by group:

Note that null or missing transaction data entries are not the same as zero-value data entries. If the balance shows as 0, a balancing transaction took place. Missing or null entries do not show as results for last_value or first_value functions. If you prefer to zero out the balance for a period, you must enter the data as a zero in the underlying data set for the final date of the period. In this case, you would enter the following data in the Stock Balances fact table:

Note that groups with separate last values can affect your results. If you search for stock balance monthly, ThoughtSpot returns the sum of all the balance values for the last date in each month. In this case, you get the following table:

Note that the balance for March is the balance for the last date in the data set, March 31st.

If you instead search for stock balance monthly group, ThoughtSpot returns a sum of all the balances for the last date in each month for each group. For March, this includes the values from March 31st and March 22nd, since the formula sums the individual values for each column. You get the following table: