Add a new field that shows rolling 12 months (Sum) of another field?

[Crispy131]

Hello, 

I have a dynamic input tool that reads in files from a folder. Every month a new file is added for the new month.

I have a field that sums the total number of people per department per month per year like below.

March 2020 Transport 305

March 2020 Retail 122

March 2020 Government 95

March 2020 Hospitality 783

April 2020 Transport 296

April 2020 Retail 171

April 2020 Government 108

April 2020 Hospitality 649

etc.

I want to add a new field that sums the total number of people per month per year for the last 12 months from that month (i.e. like a rolling 12 months sum) regardless of department. E.g. If summing up for March 2020 it should sum the people of April 2019 to March 2020. If summing up June 2021 it should sum July 2020 to June 2021 etc. I'm assuming this value will be repeated for each line of that month/year but that's okay.

The purpose of this is to get a total headcount for each month/year which I can then divide by 12 to get an average headcount and then use it to calculate a rolling turnover percentage later on with the number of people that have left in each month/year (I already have this number).

Any ideas how I can do this? Any help is much appreciated! 

Thank you

[Yoshiro_Fujimori]

Hi @Crispy131 ,

I assume that you have already put all rows in multiple files together, using Direct Input tool.

Then the next steps would be 

1. Unique tool to de-duplicate the rows (I assume the number of people is not updated over time once it appears on the report.)

2. Text To Columns tool to Parse the column into 4 columns (Month, Department , and Number of people)

3. Formula tool to add the Date column in Date type.

4. Filter tool to select the date in the past 12 months

5. Summary tool to get the sum of the Number of people by Department.

6. Formula tool to calculate the average (dividing the sum by 12).

If I miss anything, pleaase let me know.

[ShankerV · Accepted solution]

Hi @Crispy131 

One way of doing this.

Many thanks

Shanker V

[Crispy131]

Legend! This does the job thank you so much!