Last_value_in_period and first_value_in_period functions
Early Access

For a simple last_date_in_period function, keep in mind that the requirement is that the result should return the maximum date in the period, defined by a date column. Define the period with a ThoughtSpot keyword such as yearly, monthly, or weekly. The function must verify that the partition’s date is the last date in the period. This signature is the same as the existing last_value function, therefore only the function name must be updated.

Let’s say that snapshot files are received from partners at different dates. Therefore, the result should return the value from the maximum date per partner. The fourth parameter includes any mandatory columns that should be included in the comparison dates partition clause. In the majority of circumstances, these would match those defined in the second parameter. Refer to the repeated use of {partnerid}.

Say that the data model includes a date dimensional table with columns that represent date periods, such as fiscal year, fiscal month, fiscal week. You can leverage these columns instead of the standard ThoughtSpot date periods. The comparison logic should include these attribute columns when they are included in the search. Therefore, they are optional partition-by columns.

Say that the snapshot files are received from partners at different dates. In addition, the data model includes a date dimensional table with columns that represent date periods, like fiscal year, fiscal month, and fiscal week. You want a function that returns the value from the maximum date per partner, while including the date period columns as optional partition columns.

In this scenario new inventory items are created throughout the year. Their opening balance should only be included if it is the first day of the period.

For the month of March a new product, oranges, was introduced. This was on the 15th March. However, currently there are no transactions for oranges in January or February.

Let’s say you want to use first_value_in_period to find the opening balance from the beginning of the period defined in your table. The formula will give you the result that the opening balance is 20, defining the beginning of the period as January 1st. If you search for the opening balance by product, it will result in the following table:

The first date in the table is January 1st. First_value_in_period verifies that the first transaction for Oranges in not the 1st of January. Therefore, null is returned.

In this scenario, data files are not always received on the same date. The business requirement is to return the inventory balance for the last file received by the partner. In addition, if a product is not received in a subsequent file, it is assumed to be zero.

In the following example, Acme Industries included monitors with a balance of 5 in the snapshot file for March 14th. They provided a new file on March 15th, which did not include a line item for monitors. Therefore, the balance for monitors was assumed to be zero on March 15th. Vandaly Industrial included all the products in both files, and had a zero balance for monitors on March 15th.

The last date in the table is March 15th. However, the last value for Vandaly Industrial is March 14th. Last_value_in_period must return the last available date by partner, which is the 15th for Acme Industries and the 14th for Vandaly Industrial.

The following table results, with a total of 10:

The balance of Monitors is expected to be zero on the 15th March. This is because the balance for Monitors from Acme Industries was provided on the 14th. The last balance value for the partition combination of Acme Industries and Monitor is the 15th, therefore zero should be assumed for Monitors on the 15th. Note that the last_value function would return the balance of 5 for Monitors.

In this use case, the team receives account balances every day. Closed accounts do not currently have a follow-up transaction which zeroes the account out. If the last snapshot date received for the account is not the last day of data then the balance is considered to be zero.

In the following example the account, Darren, had a final snapshot balance received on March 14th. On the 15th of March, this is considered to be a value of zero.

This formula returns a result of 5. The last date in the table is March 15th. Only balances for this date are included in the result. Therefore, the account for Darren is not included in the total.

In the following example, the files are received every day. These files are at the employee level and indicate if the employee is a full-time employee.

At the start of the year, Sales and Marketing were separate departments. At the end of the year, there was a single department, Sales and Marketing. Field Tech is a department that exists at the end of the year but was not valid at the start. Human Resources existed at the start of the year but not at the end of the year.

Using the formula above, you can calculate the yearly headcount by department. The last date in the year is December. Therefore, only full-time employee values from this month should be included. Note that during the year, the departments, Sales, Marketing, and Human Resources were valid, however they have no valid records for December.

The following table results:

The total tallies to four full-time employees.