Accumulation by specific dimensions

[#1]

Hello community,

Please can you check the attached app that describes my request.

I'm trying to accumulate the sales by year.

The report is by Year, Quarter.

But when I add a 3rd dimension, like month, the accumulation breaks at the change of quarter.

The final expression (that I'll apply in the real scenario) should work regardless of the number of dimensions in the report and the number of dimensions to accumulate by.

Appreciate your help.

Thanks.

[Anonymous]

Hi!

Pay attention because in the full table (Year, Quartre, Month) Rowno() goes from 1 to 3, so at the change of Quarter the above function restart the rangesum function and you lose the cumulation.

[#3]

Hi Elena,

Thx for replying.

Indeed what you mentioned is already observed .

And what I'm looking for is a tweak/solution to be able to accumulate as needed.

[cwolf]

Hi,

to get a complete accumulation over all lines, you have to use:

sum(Sales) + RangeSum(Above(Total Sum(Sales),1,RowNo(TOTAL)-1))

or without incorrect subtotals:

if(Dimensionality()=0 or Dimensionality()=Max(Dimensionality()), sum(Sales) + RangeSum(Above(Total Sum(Sales),1,RowNo(TOTAL)-1)))

Regards

Christian

[#5]

Hi Christian,

An overall accumulation is not exactly what I'm looking for.

I want to accumulate by year even if I have more than 2 dimensions in the pivot.

Thanks.

[#6]

Any hint ?

[agomes1971]

Hi,

see attached.

HTH

André Gomes

[#8]

Hi Andre,

That's accumulating the sales overall.

I need to accumulate by Year.

Thx anyways

[cwolf]

Hi,

for this, you need an indicator if dimension Year changed - depends on the last dimension Month:

aggr(min(Month),Year) -> has only a value for the first month in year.

The new expression needs a name because of recursive calling - AccuByYear:

if(aggr(min(Month),Year)>0,SumSales,SumSales+Above(Total AccuByYear))

See attachment.

Regards

Christian

[#10]

Hi Christian,

No doubt your solution moved us closer.

But when we try to apply same in our scenario (having strings and not only numbers), we're facing some issues.

Could you please check the attached app ?

What we need is to be able to sort the pivot table by these fields in order (noting that ar_duedate2 is numeric and ar_duedate is formatted as date dd/mm/yyyy):

customer asc,

type (invoiced then unapplied),

routing (routingcode asc),

due date asc,

doc no asc

then the accumulation to be done by customer and type (so to have the accumulation restart on change of customer or type).

Appreciate yours and other community members help.