# Crosstab accumulated sum over months per year

Hi

I have a crosstab with the 2 dimensions year and month, not built as a hierarchy. I would like to calculate the accumulated sum over months, starting from 0 every year.

The formulas I use are

Akk.sum = sum(d-1, d1:0, m1) - does not start from 0 in 2021.

Akk.sum_year = sum(d-1, 0(l(1,0)), m1) - does not accumulate over the months.

I hope someone can help me :)

1

## Comments

Louise Stub HansenHi Sanne

There is for sure a much easier solution to this, than the work around I can suggest below. But if your crosstable always starts with January and always includes data for all 12 month, maybe you can use this calculation:

First create a calculated column with the syntax: mod(allcount(d1, d1:0, m1);12)

Then create a second calculated column with the syntax: if sum(c1, 0, m1) = 1 then sum(d1, 0, m1) else sum(d1, 0, m1) + sum(0, -1, m1)

Louise Stub HansenAnd then of course you can hide the first calculated column afterwards :-)

Ole DyringHi Sanne,

Even though it is not a "real" hierarchy, I believe you should still be able to use the Siblings modifier:

Akk.sum = sum(d-1, d1:0(s), m1)

BR / Ole

Sanne LiebeThanks Louise, your solution will definitely be useful in solving many future problems.

And thanks Ole, that did the trick!

I actually tried that very solution yesterday, but for some reason it didn't work. Probably a typo...

Here is the solution with an accumulated average per year:

Please sign in to leave a comment.