Rolling 12 month sum using above() in multi dimensional pivot table
Hello, I have the below table for which I was initially trying to calculate rolling 12 month sum of sales and display at month level.
Month
grade
sale
1
A
10
1
B
20
1
C
30
2
A
10
2
B
20
2
C
30
3
A
10
3
B
20
3
C
30
4
A
10
4
B
20
4
C
30
5
A
10
5
B
20
5
C
30
6
A
10
6
B
20
6
C
30
7
A
10
7
B
20
7
C
30
8
A
10
8
B
20
8
C
30
9
A
10
9
B
20
9
C
30
10
A
10
10
B
20
10
C
30
11
A
10
11
B
20
11
C
30
12
A
10
12
B
20
12
C
30
Initial output: I got this part correct.
Month
sum(aggr(rangesum(above( total sum({<Month=>}sale),0,12)),Month))
1
60
2
120
3
180
4
240
5
300
6
360
7
420
8
480
9
540
10
600
11
660
12
720
But when I pull the second dimension into the pivot table, I am not seeing expected results for Rolling 12 months values for the combination of these dimensions.
Any suggestions. I am not sure if I am doing this right. maybe above() is not the right function here.
I could do it longitudinal like this, which would give right results, but there are many such combination of dimensions in the actual data to do it this way.
Month
sum(aggr(rangesum(above( total sum({<Month=,grade={'A'}>}sale),0,12)),Month))
sum(aggr(rangesum(above( total sum({<Month=,grade={'B'}>}sale),0,12)),Month))
sum(aggr(rangesum(above( total sum({<Month=,grade={'C'}>}sale),0,12)),Month))
