Okay, works so far for aggregation over the years no matter which dimension is else in the pivot table.
Thank you so far!
Now I figured out I need to change the description of the requirement a bit.
I need to aggregate over the years but only for the dimension which come after the year.
So if the original data looks like this:
%Key1 Year SalesRep Amount A 2008 Mary 100 A 2009 Mary 200 B 2008 Joe 500 B 2009 Joe 400
I would need the output like this (accumulated over the year but still a drilldown through the dimensions in the pivot)
Year SalesRep Amount
2008 Mary 100
2009 Mary 300
2008 Joe 500
2009 Joe 900
The expression from Steve gives me:
Year SalesRep Amount 2008 Mary 600 2009 Mary 1200
i was unable to make the requested chart wthout modify the data a bit. The change is that every row has unique %Key1 field based on Year&SalesRep combination. The modify table look like this bolow:
%Key1 Year SalesRep Amount A 2008 Mary 100 B 2008 Joe 500 C 2009 Mary 200 D 2009 Joe 400
And the expression is:
aggr( rangesum( sum(Amount), above(sum(Amount),1,rowno())) ,%Key1)
Also the sort of the SalesRep must be on %Key1 field.
With this changes the result table is:
If no unique key is availabe in the datasource you can always use the Autonumber number function to make unique key based on a field combination.
Hope this helps!