Pivot table and table transformer for running totals

[Nags Subramanian]

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

Eg: 

source table:

Quarter	AM	GF	GWM
Q1 2023	1		3
Q2 2023		2
Q3 2023
Q4 2023	2
Q1 2024		3
Q2 2024			9
Q3 2024	3
Q4 2024		7
Q1 2025
Q2 2025
Q3 2025	4		12
Q4 2025		8

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

Quarter	AM	GF	GWM
Q1 2023	1	0	3
Q2 2023	1	2	3
Q3 2023	1	2	3
Q4 2023	3	2	3
Q1 2024	3	5	3
Q2 2024	3	5	12
Q3 2024	6	5	12
Q4 2024	6	12	12
Q1 2025	6	12	12
Q2 2025	6	12	12
Q3 2025	10	12	24
Q4 2025	10	20	24

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]

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.