Accumulation of values N steps back - build up of line

[#1]

Hi,

On the attached line chart I have accumulated the value 12 steps back for a moving annual total. It works fine except the first 11 months are not valid as they show the line building up from zero. In reality there was 12 months history on each of these points so I want to exclude them. The problem is that if I hide those first 11 months in the Month dimension, it hides the data from those months so the line builds up from zero 12 months further on.

If I use set analysis and try to sum over the 12 previous months it does not work because the data has already been split by the month dimension. Rangesum will not work as the data is not summarised at the correct level in the records. 

Any ideas please? I attach a picture of the chart.

[#2 · Accepted solution]

I fixed it by taking off accumulation, using Island Month on the x-axis and changing the expression to:

=sum(if([Island Year period ID]>12 and  [Year period ID] > ([Island Year period ID]-12) and [Year period ID] <= [Island Year period ID],Quantity))

The build up line then changed to a flat line for the first 12 points which I hid using dimension limits on the Island Month field.

This is more accurate as well because I found that for each line, accumulating back 12 values would not just go back 12 months but go back even further if there were months with no values, which is completely wrong.