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.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.