Cumulative summary

[KaterinaSimerdova]

Hello,

could someone please help me to create a cumulative summary of the value when on monthly basis only changes are reported?

Example below: at the beginning of 2025 (first reporting) full number of employees was reported.

Later on (on monthly basis) only changes are reported (leavers, newcomers).

Every month I would need to show the summary from the beginning of the reporting period

- e.g. for company AAA in February 1 manager in marketing (2 in Jan minus 1 in Feb).

I tried different ways, e.g. Summarize Tool and Group by, but without luck..

Thank you!!

PERIOD	COMPANY	DEPARTMENT	ROLE	VALUE
2025-01-01	AAA	MARKETING	MANAGER	2
2025-01-01	AAA	MARKETING	SPECIALIST	10
2025-01-01	AAA	FINANCE	SPECIALIST	8
2025-01-01	BBB	FINANCE	JUNIOR	20
2025-02-01	AAA	MARKETING	MANAGER	-1
2025-02-01	AAA	MARKETING	SPECIALIST	+3
2025-02-01	AAA	FINANCE	SPECIALIST	-1
2025-02-01	BBB	FINANCE	JUNIOR	0

[Vinod28]

Hi @KaterinaSimerdova 

Thanks for reaching out.

Yes, I can help with this. Your goal is to calculate the cumulative number of employees over time, starting with the full headcount in January, and then applying monthly changes (new hires or leavers) for each Company, Department, and Role.

Here’s a step-by-step approach to building this in Alteryx:

---

1. Input the Data
Use the Input Data tool to load your file (e.g., employee_monthly_changes.csv). Make sure the PERIOD column is set to a date type.

2. Sort the Records
Add a Sort Tool and sort by:

• COMPANY

• DEPARTMENT

• ROLE

• PERIOD (ascending)

This ensures the data is in the right order for cumulative calculations.

3. Handle Missing Values
Add a Formula Tool to ensure no nulls in the VALUE column:

IIF(IsNull([VALUE]), 0, [VALUE])

4. Calculate Cumulative Totals
Add a Multi-Row Formula Tool to calculate the cumulative sum.

• Group by: COMPANY, DEPARTMENT, ROLE

• Create a new field: CumulativeValue

• Formula:

IIF(IsNull([Row-1:CumulativeValue]), [VALUE], [Row-1:CumulativeValue] + [VALUE])

This will give you a running total of employees, month by month, for each role.

5. Output the Results
Use a Browse Tool to view the results or an Output Data Tool to export it as needed. 

I have attached the sample input file for your kind reference. Please check. 

[abacon]

@KaterinaSimerdova Here is a way to do it for both 2024.2 and newer and then for before 2024.2. Not sure what version you are on but this does both. It creates a field that is the Summary Amount for each month on the values specified.

Bacon

[mceleavey]

@Hi @KaterinaSimerdova ,

the other posters have given perfectly workable responses, but a cleaner way might be to simply use the Running Total tool, grouped by the appropriate fields. This will create the same as the multi-row formula tool but you won't need to build any of the formulae.

Hope this helps,

M.