Running Totals and Rolling Calculations for CSV

A CSV running total answers a different question from a grouped summary. A summary might give one total per customer. A running calculation keeps every order and adds the customer’s total at that position. The same workspace can show the previous measurement, the next measurement, the difference from the previous value, a change ratio, or an average across a fixed number of records. Original identifiers, descriptions and source positions stay with their rows. No detail rows are collapsed or removed by the calculation.

Start with the synthetic example to see the result before using private data. Group A contains values 10, 20, 0 and 30 on four successive dates. With an opening balance of zero, the running sums are 10, 30, 30 and 60. Group B starts a new calculation and does not inherit A’s total. Several results can be added in one run. All results share the selected grouping and sorting rules; each result keeps its own operation, source field, missing policy, window, opening balance and output precision.

Choose CSV, TSV or an XLSX data area, paste a table, or continue with a table already applied in this workbench. Text file import first asks for encoding, then for delimiter, header and parsing confirmation. Review quoted commas, multiline cells, trailing fields and short records. A sample preview does not approve the complete file: the full import checks every record. Original files remain read only, and exports create new copies.

For an XLSX workbook, select one sheet and an explicit data area. Inspect the header, first data row, hidden rows and hidden columns. Stored values, displayed text and cached formula results have different meanings. A formula without a usable saved value blocks import rather than appearing as an empty cell. This tool does not recalculate spreadsheet formulas or preserve workbook presentation. If an identifier relies on displayed leading zeros, explicitly choose displayed text and inspect it before importing.

Confirm the decimal separator, grouping separator, negative notation and percent interpretation used by numeric inputs. Text identifiers such as 00123 and 9007199254740993 are not automatically changed. Whitespace and the literal word NULL are not treated as empty numbers. A value that violates the confirmed numeric rules causes an issue; it is never silently converted to zero. Arithmetic uses decimal values, with bounded precision and explicit final rounding rather than binary floating point totals.

Select grouping fields in the order that identifies a customer, store, device or another independent series. With no grouping fields, the entire file is one group and the interface says so. Grouping compares complete text values without trimming or case folding. Missing keys and empty strings require review by default. You can explicitly allow them as separate groups; a missing cell and an empty string still remain distinct. Clean inconsistent keys in another tool when they should represent the same group.

Add one or more sort fields, their direction, and an interpretation of text, number or date. Text sorting uses Unicode order, so it is suitable for deliberately ordered codes but does not promise natural language collation. Numeric sorting uses the confirmed decimal rules. Date sorting needs a source format; instant sorting also requires explicit offsets or a source time zone. The browser’s location is never a hidden time zone setting. Ambiguous or nonexistent local times must be corrected with explicit offsets.

When all sort keys are equal, their order affects cumulative totals and neighbors. The first run reports these ties. Add a second sorting key or explicitly confirm the source order as the final tie breaker. If you choose no sorting fields, source order also needs confirmation. Empty or invalid sort values block the affected group because calculating an apparently valid subset could give misleading totals. Other valid groups can still be inspected, but unresolved ordering issues block the main export.

Running sum starts at the opening balance and includes the current record. Running row count counts all record positions, including rows with empty business values. Previous and next value refer strictly to the immediate neighbor within the group. The first record has no previous value; the last has no next value. These expected empty results are not errors. Difference subtracts the previous value from the current value. Change ratio divides that difference by the previous value. A zero previous value is a separate error. A negative base can produce a negative ratio even when the current value increases.

A rolling window of N records contains the current record and up to N minus one earlier records in the same group. It is a record window, not a period of N days. Repeated timestamps occupy separate positions, and absent dates do not create positions. In the normal example, a three-record rolling sum gives 10, 30, 30 and 50. With minimum valid observations set to one, the averages are 10, 15, 10 and 50 divided by 3. At two output decimal places, that final average is 16.67.

Set the minimum number of valid observations required for a rolling result. The default is one and is visible in the editor. Requiring N valid observations leaves early or incomplete windows empty. Those cells mean insufficient observations, not zero and not a calculation failure. Open the result preview to inspect the actual inclusive start and end positions in the calculation order list. The source button retrieves every contributing input record for that frame. The complete trace remains available in its own report.

The default missing policy blocks calculations whose required frame contains a missing cell or empty string. In a running sum, one unresolved missing amount affects that record and later totals within its group. In a rolling sum it affects only frames that contain the missing position. Ignore skips the empty value’s contribution but keeps the position. For values 10, empty and 20 with a two-record window, the last mean is 20 because the window contains empty and 20. Deleting the blank row and using 10 would incorrectly produce 15.

The zero policy is an explicit replacement for missing inputs in that result column, with a replacement count. Invalid numeric text remains an error under every missing policy. Ignoring blanks does not make previous or next jump to a farther nonempty record. Their result stays empty when the immediate neighbor is empty. Means divide by the number of valid observations in the existing frame, including explicitly substituted zeros when that policy is selected.

A running sum can use a fixed opening value, initially zero, or a separately imported group lookup table. For a lookup, choose the table, select its key fields in the same order as the main grouping fields, and choose its opening value column. Every group must match exactly one lookup row. Missing groups, duplicate matches and invalid opening values are reported; the tool never chooses the first match or silently substitutes zero. Each sum can use a different opening value column from that same lookup table.

Choose whether the final table restores original row order or displays groups in calculation order. This changes presentation only: each row keeps the same calculated values. The calculation order report explains how it was evaluated, and the trace identifies each result’s frame. Input row count equals output row count. Failed result cells and rows containing failures are separate statistics because one source row can have several failed calculations. Expected empty results have their own count.

Rule edits make earlier results stale and stop them from being downloaded as current. Undo and redo rule edits, save a rule file, or apply a reviewed result to the workflow and undo that applied step later. Saved rules can contain field names and fixed values, so inspect them before sharing. Loading rules against uniquely named reordered fields remaps their stable references and asks for numeric and ordering confirmation again. Rule files do not automatically include the complete source table.

Export the main table, issue records, detailed results, order list or rule summary. Numerical errors require review and acknowledgment before exporting an all-row main table with empty failed cells. Grouping, ordering and opening lookup conflicts must actually be resolved. CSV and TSV downloads are independently read back before download; a ZIP bundles selected report types as separate files. Spreadsheet protection can prefix formula-like text and therefore change legitimate negative values. Review the affected count and use the explicit raw-text path when your destination requires unchanged machine values.

Processing runs in a cancellable browser worker. Source or rule changes invalidate prior results, and an old task cannot overwrite a later result. Limits include 10 MB per file, 100000 source rows, 200 columns, 20 result columns, bounded report size and a 30-second task timeout. Complex reports can reach their limits earlier than the source table. Content stays in browser memory; refreshing or closing clears the session. The webpage still requests static resources. No account synchronization, remote source fetching or universal spreadsheet compatibility is promised.