Aggregations

This feature is currently under development and, although not available in the current version, its preliminary documentation is provided to give you a preview of the enhancements that will be included in an upcoming update.

Context

Aggregations are a powerful feature of the public API, allowing users to compute summary statistics such as totals, averages, and counts directly on the server.
This reduces the amount of data transferred to the client, optimizing performance and ensuring efficiency in data analysis.

The API enables aggregations through grouping and aggregation functions.
By leveraging these capabilities, users can obtain pre-aggregated data in a structured manner, which is particularly useful in financial and accounting applications.

A key advantage is that users can filter the data to be aggregated using all filtering options supported by DataLoaders.
This means filters can be applied not only to the main entity but also to its parent entity (if it exists) and its child entities (if they exist).
This provides extensive flexibility in refining aggregated results, ensuring that only relevant data is considered while maintaining optimal performance.

Some Use Case Examples

Advantages

Notes:
  • Aggregations allow for flexible grouping using different time periods such as Day, Week, Month, Quarter, and Year.
  • Aggregation is defined using two main components:
    • aggregateFields: Specifies the fields to be aggregated (e.g., sum of amounts).
    • groupFields: Defines how the data should be grouped (e.g., by account, by date).
  • The API’s aggregation capabilities enable users to perform high-level financial analysis with minimal overhead.

Example of Query

The following query demonstrates how aggregations work.
It retrieves accounting entry lines where the subAccount code starts with 6 or 7, within the specified date range, and computes total sums for creditAmount and debitAmount, grouped by subAccountId and accountingEntry.date (by Quarter).

Only the aggregation results and grouping values are returned in the response, not the detailed lines used for the calculations.

query {
  accountingEntryLines(
    where: {
      and: [
        { accountingEntry: { date: { gte: "2021-01-01", lte: "2025-01-31" } } }
        { subAccount: {
            or: [
              { code: { startsWith: "6" } },
              { code: { startsWith: "7" } }
            ]
          }
        }
      ]
    }
  ) {
    edges {
      node {
        creditAmount
        debitAmount
        subAccountId
        accountingEntry {
          date
        }
      }
    }
    totalSum(
      aggregateFields: ["creditAmount", "debitAmount"],
      groupFields: ["subAccountId", "accountingEntry.date|Quarter"]
    ) {
      groupValues {
        name
        value
      }
      aggregates {
        name
        value
      }
    }
    pageInfo {
      endCursor
      hasNextPage
    }
  }
}

Addressing Aggregations with Grouping

Aggregations work by grouping data using specified fields and then applying aggregation functions to compute summary values.

Important Points and Limitations