Create a custom aggregation

Functions can be used to compute custom aggregations based on data in the ontology, which can then be surfaced in a Chart widget in Workshop. This guide walks through how to write custom aggregation logic that loads aggregated data from the ontology, manipulates the results to create a projection of future results, and returns the modified results.

These references may be useful while working through this section:

• Reference for Object Set aggregations
• Reference for aggregation types

Loading an aggregation

In this example, suppose we have an ontology consisting of expenses, each of which include an organization's department name, a date, and an expense amount. We want to estimate the monthly spend by department over the next six months. We can begin by loading the aggregated data for the monthly spend:

Copied!
1
2
3
4
5
const result = await Objects.search()
    .expenses()
    .groupBy(expense => expense.departmentName.topValues())
    .segmentBy(expense => expense.date.byMonth())
    .sum(expense => expense.amount);

Manipulating aggregation results

Next, we can extrapolate the spend for each department for the next six months. For the sake of example, let's use the very naive approach of simply using the final month's value as the estimate for the next six months.

Copied!
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
const modifiedBuckets = result.buckets.map(bucket => {
    // Find the bucket corresponding to the most recent month
    const lastBucket = bucket.value[bucket.value.length - 1];

    let nextSixMonths: IBaseBucket<IRange<Timestamp>, Double>[] = [];
    let currentMonth = lastBucket.key.max!;
    // Loop six times
    for (let i = 0; i < 6; i++) {
        // Find the end of this range (the following month)
        const nextMonth = currentMonth.plusMonths(1);
        // Add a new bucket which uses the next month as the date range
        // and the most recent month as the value
        nextSixMonths.push({
            key: {
                min: currentMonth,
                max: nextMonth,
            },
            value: lastBucket.value,
        });
        currentMonth = nextMonth;
    }

    // Return the modified results
    return { key: bucket.key, value: nextSixMonths };
});

Returning the aggregation

Now that we have created an estimate for the next six months, we can just return these estimated values:

Copied!
1
return { buckets: modifiedBuckets };

All together, here is the full example code for this Function:

Copied!
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
@Function()
public async estimatedDepartmentExpenses(): Promise<ThreeDimensionalAggregation<string, IRange<Timestamp>>> {
    const result = await Objects.search()
        .expenses()
        .groupBy(expense => expense.departmentName.topValues())
        .segmentBy(expense => expense.date.byMonths())
        .sum(expense => expense.amount);
    
    const modifiedBuckets = result.buckets.map(bucket => {
        // Find the bucket corresponding to the most recent month
        const lastBucket = bucket.value[bucket.value.length - 1];

        let nextSixMonths: IBaseBucket<IRange<Timestamp>, Double>[] = [];
        let currentMonth = lastBucket.key.max!;
        // Loop six times
        for (let i = 0; i < 6; i++) {
            // Find the end of this range (the following month)
            const nextMonth = currentMonth.plusMonths(1);
            // Add a new bucket which uses the next month as the date range
            // and the most recent month as the value
            nextSixMonths.push({
                key: {
                    min: currentMonth,
                    max: nextMonth,
                },
                value: lastBucket.value,
            });
            currentMonth = nextMonth;
        }

        // Return the modified results
        return { key: bucket.key, value: nextSixMonths };
    });

    return { buckets: modifiedBuckets };
}

The resulting aggregation can be used in a Workshop Chart to show the monthly spend estimate for the next six months.