Running Total (Year to Date)

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. 1st July YTD actual would be equal to the Month Actual (204), 1st August YTD actual would be equal to the 1st July YTD plus 1st 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

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