How do I create a segmented cumulative sum graph in Palantir Foundry Workshop?

Question

I have some objects that represent maintenance jobs that look like this:

Due On	Status
2021-12-01	open
2022-06-17	open
2022-07-05	closed
2022-07-05	closed
2022-08-01	open
2023-09-02	open

How can I generate a segmented cumulative graph in Palantir Foundry Workshop, plotting values like this?

Answer 1

You can do this using a Foundry Function. Create a TypeScript functions repository and use the following code (see comments inline for an explanation):

import { Function, Double, ThreeDimensionalAggregation, IRange, IRangeable, Timestamp, BucketKey, BucketValue } from "@foundry/functions-api";

// Replace MaintenanceJob with your object
// To make your object available, add it in the Settings > Ontology tab
import { ObjectSet, MaintenanceJob } from "@foundry/ontology-api";

export class MyFunctions {
    // You will find this function in Workshop after it's published
    // Replace MaintenanceJob with your object
    @Function()
    public async cumulativeJobsByMonthByStatus(jobs: ObjectSet<MaintenanceJob>): Promise<ThreeDimensionalAggregation<IRange<Timestamp>, string, Double>> {
        const bucketedJobs = await jobs
            .groupBy(j => j.dueOn.byMonth())
            .segmentBy(j => j.status.topValues())
            .count();

        const sortedBucketedJobs = sortBuckets(bucketedJobs);
        const cumulativeSortedBucketedJobs = cumulativeSum3D(sortedBucketedJobs);

        return cumulativeSortedBucketedJobs
    }
}

/**
 * Sort buckets of a 2D or 3D aggregation by the first axis in ascending order
 * 
 * Example input 1:
 * { buckets: [
 *   { key: { min: "2022-01-01", max: "2022-12-31" }, value: 456 },
 *   { key: { min: "2021-01-01", max: "2021-12-31" }, value: 123 },
 *   { key: { min: "2023-01-01", max: "2023-12-31" }, value: 789 },
 * ]}
 * 
 * Example output 1:
 * { buckets: [
 *   { key: { min: "2021-01-01", max: "2021-12-31" }, value: 123 },
 *   { key: { min: "2022-01-01", max: "2022-12-31" }, value: 456 },
 *   { key: { min: "2023-01-01", max: "2023-12-31" }, value: 789 },
 * ]}
 * 
 * Example input 2:
 * { buckets: [
 *   { key: 22, value: 456 },
 *   { key: 21, value: 123 },
 *   { key: 23, value: 789 },
 * ]}
 * 
 * Example output 2:
 * { buckets: [
 *   { key: 21, value: 123 },
 *   { key: 22, value: 456 },
 *   { key: 23, value: 789 },
 * ]}
 * 
 * Example input 3:
 * { buckets: [
 *   { key: { min: "2022-01-01", max: "2022-12-31" }, value: [{ key: "open", value: 789 }, { key: "closed", value: 910 }] },
 *   { key: { min: "2021-01-01", max: "2021-12-31" }, value: [{ key: "open", value: 123 }, { key: "closed", value: 456 }] },
 *   { key: { min: "2023-01-01", max: "2023-12-31" }, value: [{ key: "open", value: 314 }, { key: "closed", value: 42 }] },
 * ]}
 * 
 * Example output 3:
 * { buckets: [
 *   { key: { min: "2021-01-01", max: "2021-12-31" }, value: [{ key: "open", value: 123 }, { key: "closed", value: 456 }] },
 *   { key: { min: "2022-01-01", max: "2022-12-31" }, value: [{ key: "open", value: 789 }, { key: "closed", value: 910 }] },
 *   { key: { min: "2023-01-01", max: "2023-12-31" }, value: [{ key: "open", value: 314 }, { key: "closed", value: 42 }] },
 * ]}
 */
function sortBuckets<K1 extends BucketKey, K2 extends BucketKey, V extends BucketValue>(buckets: ThreeDimensionalAggregation<K1, K2, V>): ThreeDimensionalAggregation<K1, K2, V>;
function sortBuckets<K extends BucketKey, V extends BucketValue>(buckets: TwoDimensionalAggregation<K, V>): TwoDimensionalAggregation<K, V>;
function sortBuckets<K extends BucketKey, V>(buckets: { buckets: { key: K, value: V}[] }): { buckets: { key: K, value: V}[] } {
    return {
        // See https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/sort
        buckets: buckets.buckets.sort(({ key: k1 }, { key: k2 }) => {
            if (typeof k1 !== typeof k2) throw new Error("Inconsistent bucket key types")

            // If not objects, these must be either numbers or booleans which can be compared like this
            if (typeof k1 !== "object" || typeof k2 !== "object") return Number(k1) - Number(k2);

            // If a bucket doesn't have a minimum, it suggests that it is the global unbounded minimum bucket, so must be lower
            if (!(k1 as IRange<IRangeable>).min) return -1;
            if (!(k2 as IRange<IRangeable>).min) return 1;

            // Otherwise, compare both buckets' minimums
            return (k1 as IRange<IRangeable>).min!.valueOf() - (k2 as IRange<IRangeable>).min!.valueOf();
        }),
    };
}

/**
 * Calculates a cumulative sum for a ThreeDimensionalAggregation over numbers, along the first axis and segmented by the second axis
 * The order of the buckets into the function matters for how the values are aggregated
 * 
 * Example input 1:
 * { buckets: [
 *   { key: { min: "2021-01-01", max: "2021-12-31" }, value: [{ key: "open", value: 123 }, { key: "closed", value: 456 }] },
 *   { key: { min: "2022-01-01", max: "2022-12-31" }, value: [{ key: "open", value: 789 }, { key: "closed", value: 910 }] },
 *   { key: { min: "2023-01-01", max: "2023-12-31" }, value: [{ key: "open", value: 314 }, { key: "closed", value: 42 }] },
 * ]}
 * 
 * Example output 1:
 * { buckets: [
 *   { key: { min: "2021-01-01", max: "2021-12-31" }, value: [{ key: "open", value: 123 }, { key: "closed", value: 456 }] },
 *   { key: { min: "2022-01-01", max: "2022-12-31" }, value: [{ key: "open", value: 912 }, { key: "closed", value: 1366 }] },
 *   { key: { min: "2023-01-01", max: "2023-12-31" }, value: [{ key: "open", value: 1226 }, { key: "closed", value: 1408 }] },
 * ]}
 * 
 * Example input 2:
 * { buckets: [
 *   { key: { min: "2023-01-01", max: "2023-12-31" }, value: [{ key: "open", value: 314 }, { key: "closed", value: 42 }] },
 *   { key: { min: "2022-01-01", max: "2022-12-31" }, value: [{ key: "open", value: 789 }, { key: "closed", value: 910 }] },
 *   { key: { min: "2021-01-01", max: "2021-12-31" }, value: [{ key: "open", value: 123 }, { key: "closed", value: 456 }] },
 * ]}
 * 
 * Example output 2:
 * { buckets: [
 *   { key: { min: "2023-01-01", max: "2023-12-31" }, value: [{ key: "open", value: 314 }, { key: "closed", value: 42 }] },
 *   { key: { min: "2022-01-01", max: "2022-12-31" }, value: [{ key: "open", value: 1103 }, { key: "closed", value: 952 }] },
 *   { key: { min: "2021-01-01", max: "2021-12-31" }, value: [{ key: "open", value: 1226 }, { key: "closed", value: 1408 }] },
 * ]}
 */
const cumulativeSum3D = <T extends BucketKey, U extends BucketKey>(buckets: ThreeDimensionalAggregation<T, U, number>): ThreeDimensionalAggregation<T, U, number> => {
    // This holds the running total for each secondary axis value
    // e.g. you `{ open: 123, closed: 456 }` at some point in the examples above
    const cumulativeBuckets = new Map<U, number>();
    
    return {
        buckets: buckets.buckets.map(b => ({
            key: b.key,
            value: b.value.map(v => {
                // Update the running total with the value we are seeing
                cumulativeBuckets.set(v.key, (cumulativeBuckets.get(v.key) ?? 0) + v.value)

                return {
                    key: v.key,
                    // Use the running total value
                    value: cumulativeBuckets.get(v.key)!,
                }
            })
        }))
    }
}

Commit your changes and publish a function version. There is a step-by-step guide in the Palantir Foundry documentation on how to create a repository and publish functions.

In your Workshop, you can create a 'Chart: XY' widget. As the data source, select the function you created and pass in the relevant object set. There's also Palantir Foundry documentation on using a derived aggregation in Workshop.

Answer 2

You can probably also do this without code by creating the chart in a Quiver analysis and then using a Quiver Dashboard to control the layout and embed it within your Workshop app.