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 :)
Louise Stub Hansen 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)
Louise Stub Hansen And then of course you can hide the first calculated column afterwards :-)
Ole Dyring 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
Sanne Liebe 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:
Comments
4 comments