Crosstab accumulated sum over months per year


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 :)



  • Hi 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)


  • And then of course you can hide the first calculated column afterwards :-)

  • Hi 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

  • Thanks 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.

Didn't find what you were looking for?

New post