Firstly, why aggregate in the loader,we try to avoid that unless a very real need to do it ?
Create a master calendar , one that has weeks for you and then in your app you will make calculation expressions to return values for Year/Week periods and so on.
Here is a link to start the ball rolling for you with Calendars ...
This data set is over 30 million data rows, and many more columns, with multiple tables extended off.
Hence why I'm trying to do essentially as much processing as possible in the load.
I don't care about days at all, the original data is actually a calendar entry but I literally only need the Year and Week, it's easier to deal with just those two than having them in one, that is a very minor part of this and I don't think it really adds to the complexity of it anyway.
Even adding up the for 'this' single year, all the weeks, takes a couple of seconds to computer adding more just makes it slower and slower until it times out and won't display the data.
The following script will add the data from the previous year.
Load * inline
2016, 1, 10, 15
2016, 2, 5, 10
2015, 1, 12, 18
2015, 2, 6, 18
2014, 1, 3, 12
Left Join (Data)
Year + 1 as Year,
A as A_LY,
B as B_LY
Left Join (Data)
(B+A) / (B_LY + A_LY) as C
Incase anyone was wondering the best way I found to solve this is to filter into a another table without the current years, then we can just sum and divide over the fields as the year is irrelevant, then just attaching it to the newest year as necessary.
Looking to do the same with weeks now, most likely going to do the same but with a do for 1...52 instead.
Let me know if there is an easier way of doing this.