Descriptive Statistic & Basic Maths functions

APIVOT

Reference

» Reference, Discussion, & Example Applications:

About

Completes the pivot table layout “cosmetics” with labels and grand totals calculations.

Calls PIVOT.

Inputs:

• r, c, v, fn: same arguments as the PIVOT function
• [p] : pivot table name label, if omitted “PT”
• [tc] : trailing column label, if omitted “GT”
• [tr] : trailing row label, if omitted “GT”)

More Info:

NOTE: If fn returns more than a single value, those values will be text joined.

Code

APIVOT = LAMBDA(r, c, v, fn, [p], [tr], [tc],
    LET(
        ur, SORT(UNIQUE(r)),
        uc, TOROW(SORT(UNIQUE(c))),
        x, VSTACK(IF(p = "", "PT", p), ur, IF(tc = "", "GT", tc)),
        y, VSTACK(uc, PIVOT(r, c, v, fn), PIVOT(, c, v, fn)),
        z, VSTACK(IF(tr = "", "GT", tr), PIVOT(r, , v, fn), PIVOT(, , v, fn)),
        a, HSTACK(x, y, z),
        FILTER(FILTER(a, TAKE(a, 1) <> 0), TAKE(a, , 1) <> 0)
    )
);