Running total with reset, lambda

Brass Contributor

I use RunTotCols below to do a running total on a column:

 

EatCols = LAMBDA(arr, LET(rows, ROWS(arr), res, N(SEQUENCE(, rows) >= SEQUENCE(rows)), res));

// Converts an array's columns into a running total
RunTotCols = LAMBDA(arr, LET(res, TRANSPOSE(MMULT(TRANSPOSE(arr), EatCols(arr))), res));

 

And I use Reset below to determine when a column's value changes:

 

PushRows = LAMBDA(addarr, target,
    LET(
        newrowct, ROWS(addarr),
        oldrowct, ROWS(target),
        choparr, CHOOSEROWS(target, SEQUENCE(oldrowct - newrowct)),
        r, VSTACK(addarr, choparr),
        r
    )
);

Reset =LAMBDA(col,
  LET(
    prevcol, PushRows({""}, col),
    same, (col = prevcol) + (SEQUENCE(ROWS(col))=1),
    same
));

 

I need to combine these ideas so that I get a running total that resets when a corresponding label column changes, but how to do this escapes me. Any suggestions? Thanks so much.

1 Reply

Hello @boukasa 
Try this example:

RunTotColsWithReset = LAMBDA(arr, labels,
    LET(
        rows, ROWS(arr),
        cols, COLUMNS(arr),
        resetFlags, Reset(labels),
        prevResetFlags, PushRows({0}, resetFlags),
        resetStarts, IF(resetFlags > prevResetFlags, SEQUENCE(rows), ""),
        resetGroups, COUNTA(FILTER(resetStarts, resetStarts <> "")),
        resetIndices, MMULT((SEQUENCE(rows) >= TRANSPOSE(resetStarts))*(SEQUENCE(rows, cols, 1, 0) = 0), SEQUENCE(resetGroups)),
        res, IFERROR(XLOOKUP(resetIndices, SEQUENCE(resetGroups), RunTotCols(arr))),
        res
    )
);

This function first calculates the reset flags for the label column using your Reset function. It then determines where each reset starts and groups the resets together. For each group, it calculates the running total using your RunTotCols function. The XLOOKUP function is then used to map these running totals back to the original array.

Note that this is just only a suggestion, and you might need to adjust it accordingly based on your specific needs. Also, this function assumes that your RunTotCols and Reset functions work as expected.