Pivot table and table transformer for running totals

Nags Subramanian April 24, 2024

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

 

Eg: 

source table:

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

 

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

QuarterAMGFGWM
Q1 2023103
Q2 2023123
Q3 2023123
Q4 2023323
Q1 2024353
Q2 20243512
Q3 20246512
Q4 202461212
Q1 202561212
Q2 202561212
Q3 2025101224
Q4 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.

1 answer

3 votes
Stiltsoft support
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
April 24, 2024

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:

Wed 16-1.png

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'

Wed 16-2.png

Hope it helps your case.

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events