Reaggregation Scenario 3: Average period value for semi-additive numbers I

Semi-additive numbers may be aggregated across some, but not all, dimensions. They commonly apply to specific time positions. In this scenario, we have daily position values for home loans, and therefore cannot aggregate on the date dimension.

Valid solution

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

average(group_aggregate(sum(loan balance),
                        query_groups() + {date(balance date)},
                        query_filters()))
Average period value semi-additive numbers

Resolution

  1. The sum(loan balance) function aggregates to the following attributes:

    • {date(balance date)} and query_groups()

      Add additional search columns to this aggregation. Here, this at the yearly level.

    • query_filters ( )

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

  2. The sum(loan balance) function returns a result for each row in this virtual table.

  3. The outer average() function reaggregates the final output as a single row for each year value.

Related information

Was this page helpful?
×