Pivot table and table transformer for running totals

Question

Hi, i need a pivot table created with a running total.

Eg:

source table:

QuarterAMGFGWMQ1 20231 3Q2 2023 2 Q3 2023   Q4 20232  Q1 2024 3 Q2 2024  9Q3 20243  Q4 2024 7 Q1 2025   Q2 2025   Q3 20254 12Q4 2025 8

Target table after table transformer macro should be using the running totals.

QuarterAMGFGWMQ1 2023103Q2 2023123Q3 2023123Q4 2023323Q1 2024353Q2 20243512Q3 20246512Q4 202461212Q1 202561212Q2 202561212Q3 2025101224Q4 2025102024

is this possible? in Excel there is a pivot setting for totals as 'Running total'. can the same result be achieved using table transformer?

first table above is a pivot table. second table i am trying to achieve using a table transformer.

please let me know.

thanks

Nagarajan.

Stiltsoft support · Answer

Hi @Nags Subramanian ,

You may use two options for the case.

The first one is to enable cumulative count in your Pivot Table macro. Please take a look at this abstract about where to find the corresponding checkbox: https://docs.stiltsoft.com/tfac/cloud/managing-pivot-table-options-42239653.html#Managingpivottableoptions-Enablethecumulativecount

The second option is to use the Table Transformer macro where you introduce the 'Quarter End Date' column:

SELECT *,
FORMATDATE(DATEADD(day, -1, DATEADD(quarter, 1, 'Quarter')), "M dd, yy")
AS 'Quarter End Date',
SUM (TT2.'Col A') AS 'Col A',
SUM (TT2.'Col B') AS 'Col B'
FROM T1 AS TT1
JOIN T1 AS TT2 on TT1.'Quarter' >= TT2.'Quarter'
GROUP BY TT1.'Quarter'
ORDER BY TT1.'Quarter'

Hope it helps your case.