
1. Re: Rolling Twelve Months calculation without using table calc
Zhouyi Zhang Jul 18, 2018 6:49 AM (in response to Brendan van Rijn)1 of 1 people found this helpfulHi, Brendan
Please see my workaround, but still need to use the table calculation (I don't think there is way using just calculation to get last 12 month rolling sum)
Hope this helps
ZZ

2. Re: Rolling Twelve Months calculation without using table calc
Alex Kerin Jul 18, 2018 8:17 AM (in response to Zhouyi Zhang)The only other way I can see to do this and not pad using missing values is this horror show of a table calc where we explicitly test datediff:
if datediff('month',lookup(attr(DATEtrunc('month',[Date])),11),attr(DATEtrunc('month',[Date])))<=11 then zn(LOOKUP(sum([Turnover]),11)) else 0 END +
if datediff('month',lookup(attr(DATEtrunc('month',[Date])),10),attr(DATEtrunc('month',[Date])))<=11 then zn(LOOKUP(sum([Turnover]),10)) else 0 END+
if datediff('month',lookup(attr(DATEtrunc('month',[Date])),9),attr(DATEtrunc('month',[Date])))<=11 then zn(LOOKUP(sum([Turnover]),9)) else 0 END+
if datediff('month',lookup(attr(DATEtrunc('month',[Date])),8),attr(DATEtrunc('month',[Date])))<=11 then zn(LOOKUP(sum([Turnover]),8)) else 0 END+
if datediff('month',lookup(attr(DATEtrunc('month',[Date])),7),attr(DATEtrunc('month',[Date])))<=11 then zn(LOOKUP(sum([Turnover]),7)) else 0 END+
if datediff('month',lookup(attr(DATEtrunc('month',[Date])),6),attr(DATEtrunc('month',[Date])))<=11 then zn(LOOKUP(sum([Turnover]),6)) else 0 END+
if datediff('month',lookup(attr(DATEtrunc('month',[Date])),5),attr(DATEtrunc('month',[Date])))<=11 then zn(LOOKUP(sum([Turnover]),5)) else 0 END+
if datediff('month',lookup(attr(DATEtrunc('month',[Date])),4),attr(DATEtrunc('month',[Date])))<=11 then zn(LOOKUP(sum([Turnover]),4)) else 0 END+
if datediff('month',lookup(attr(DATEtrunc('month',[Date])),3),attr(DATEtrunc('month',[Date])))<=11 then zn(LOOKUP(sum([Turnover]),3)) else 0 END+
if datediff('month',lookup(attr(DATEtrunc('month',[Date])),2),attr(DATEtrunc('month',[Date])))<=11 then zn(LOOKUP(sum([Turnover]),2)) else 0 END+
if datediff('month',lookup(attr(DATEtrunc('month',[Date])),1),attr(DATEtrunc('month',[Date])))<=11 then zn(LOOKUP(sum([Turnover]),1)) else 0 END
+zn(LOOKUP(sum([Turnover]),0))

3. Re: Rolling Twelve Months calculation without using table calc
Brendan van Rijn Jul 19, 2018 7:28 AM (in response to Alex Kerin)Thanks Alex! It really did help a lot. Just have to find a way to bypass the empty rows, as those are ignored, unfortunately