Use case

You have a table (a pivot table) containing a sequence of years and some numeric values.

You need to calculate cumulative (running) totals when the value of each next year is added to the sum of the previous ones.

Solution

  1. Switch the page to the edit mode.
  2. Insert the Table Transformer macro and paste the table within the macro body.
  3. Select the macro and click Edit.
  4. Switch to the SQL query tab.

  5. Enter the following SQL query:

    SELECT 'Year', 
SUM (TT2.'Requests') AS 'Requests'
FROM T1 AS TT1
JOIN T1 AS TT2 on TT1.'Year' >= TT2.'Year'
GROUP BY TT1.'Year'
ORDER BY TT1.'Year'
  6. Click Next.
  7. Define the table settings and view options if needed.
  8. Save the macro and the page.

As an alternative, since the 7.4.0 version of the app, you can enable the Cumulative count option in the Pivot Table macro.

As an alternative you can enable the Cumulative count option in the Pivot Table macro.

You can use FORMATWIKI function for the purposes of cell formatting.