Reaggregation Scenario 2: Average rates of exchange

The Average rate of exchange calculates for the selected period. These average rates provide a mechanism to hedge the value of loans against price fluctuations in the selected period. We apply the average rate after the aggregation.

The pseudo-logic that governs the value of loans is sum(loans) * average(rate).

The data model has two tables: a primary fact table, and a dimension table for rates.

  • The loans column is from the primary fact table.

  • The rate column is from the rates table.

These tables are at different levels of aggregation:

  • The primary fact table uses a lower level of aggregation, on product, department, or customer.

  • The rates dimension table use a higher level of aggregation, on daily, transaction currency, or reporting currency.

The two tables are joined through a relationship join on date and transaction currency.

To simplify the scenario, we only use a single reporting currency. The join ensures that a single rate value returns each day for each transaction currency.

Valid solution

A valid query that meets our objective may look something like this:

sum(group_aggregate (sum(loans)*average (rate),
                     query_groups () + {transaction_currency},
                     query_filters () ))

The following search and resulting response returns the dollar value for each year, for each target reporting currency. Note that the dataset contains both euro (€) and US dollars ($). The $ Loans Avg. Rate calculates the average rate of exchange for the entire period. The $ Loans Spot Rate applies the rate of exchange on the day of the transaction.

Average rates of exchange

Resolution

  1. The sum(loans) function aggregates to these attributes:

    • {transaction_currency} and query_groups()

      Add additional search columns to this aggregation. Here, this at the level of reporting currency and year.

    • query_filters( )

      Applies any filters entered in the search. Here, there are no filters.

  2. Similarly, the average(rate) function aggregates to these attributes:

    • {transaction_currency} and query_groups()

      Add additional search columns to this aggregation. Here, this at the level of reporting currency and year.

    • query_filters( )

      Applies any filters entered in the search. Here, there are no filters.

  3. For each row in this virtual table, the exchange rate applies to the sum of loans: sum(loans) * average(rate).

  4. The outer sum() function reaggregates the final output as a single row for each yearly reporting currency value.

    The default aggregation setting does not reaggregate the result set.

Non-aggregated result

We include the following result to provide contrast to an example where ThoughtSpot does not reaggregate the result set. Reaggregation requires the aggregate function, sum, to precede the group_aggregate function.

In the following scenario, the formula assumes that the default aggregation applies. Here, the result returns 1 row for each transaction currency.

group_aggregate (sum(loans )*average (rate ),
                 query_groups() + {transaction_currency},
                 query_filters())
Average rates of exchange

Related information

Was this page helpful?
×