Running Total (Year to Date)

[cmather]

I would like create a running total (Year to Date) column based on adding the total of the current month’s data to that of the previous months so far in any financial year.

e.g. 1^st July YTD actual would be equal to the Month Actual (204), 1^st August YTD actual would be equal to the 1^st July YTD plus 1^st August month actual (420)  and so on.

The data needs to be grouped by Financial Year, Group, Division and Measure see the sample table below.

FinancialYear	ReportMonth	Group	Division	Measure	MonthActual
2015/2016	2015-07-01	Group 1	Fred Blogs Inc	Data Type 1	204
2015/2016	2015-08-01	Group 1	Fred Blogs Inc	Data Type 1	216
2015/2016	2015-09-01	Group 1	Fred Blogs Inc	Data Type 1	210
2015/2016	2015-10-01	Group 1	Fred Blogs Inc	Data Type 1	233
2015/2016	2015-11-01	Group 1	Fred Blogs Inc	Data Type 1	209
2015/2016	2015-12-01	Group 1	Fred Blogs Inc	Data Type 1	180
2015/2016	2016-01-01	Group 1	Fred Blogs Inc	Data Type 1	165
2015/2016	2016-02-01	Group 1	Fred Blogs Inc	Data Type 1	184
2015/2016	2016-03-01	Group 1	Fred Blogs Inc	Data Type 1	212
2015/2016	2016-04-01	Group 1	Fred Blogs Inc	Data Type 1	186
2015/2016	2016-05-01	Group 1	Fred Blogs Inc	Data Type 1	180
2015/2016	2016-06-01	Group 1	Fred Blogs Inc	Data Type 1	29

 

I have managed to do this with just on Financial Year, Group Division and Measure however there are multiple Groups, Divisions and Measures to take into consideration in our data.

I have looked at the Group by tool and Java Snippet tool but I am not a Java programmer so I didn’t get very far with that. Any help would be apreciated

[David_Ko]

Hi

You can use a Group Loop to split the data into financial year groups and then use a moving aggregation node with the cumulative computation checked to do the running sum. The data needs to be sorted by the financial year groupings first.

Attahced is an example. I've taken your data, and duplicated it with an extra financial year (2014/2015) to demonstrate.

David

 

• running_total_year_to_date.zip