Cumulate sumproduct with rangesum over two dimensions

[Anonymous]

Hello,

i need to cumulate the values for every Customer (Dimension 1) and the displayed Quarters (Dimension 2).

My Formular for Column "Average" is  a Sumproduct and calculated by:

=SUM(Total <CUSTOMER_NAME, QUARTER>AGGR($(vAverageBruttoByCustomer),CUSTOMER_NAME, PLACE, QUARTER))

$(vAverageBruttoByCustomer) is calculated in the script by:

set vAverageBruttoByCustomer = '(Sum(Brutto_Place)/SUM(Customer_Place)*sum(total<CUSTOMER_NAME, PLACE, YEAR> Value))/(sum(total<CUSTOMER_NAME, YEAR> Value))';

Like in the Screenshot marked, I want the cumulation only for the Quarters per Year. 2014Q1-2014Q4 and then start again for 2015Q1-2015Q4.

How does it work?

tried this... but doesn't work like i want

The Column "cumul. Average" is based on the calculation of "Average" and calculated like this:

=if((left (QUARTER, 4)= above(left (QUARTER, 4))),RangeSum(Above(Column(1), 0, RowNo())), RangeSum(Column(1)))

I also tried the solution by stalwar1‌ of the discussion: Re: Rangesum cumulative with 2 dimensions

RangeSum(Above(Column(1), 0, RowNo())) for  "cumul. Average" - does not work in my case.

Can anyone help to solve the problem? Thanks very much!!!

Nachricht geändert durch Tanja Steiner

[sunny_talwar]

Would you be able to share a sample to test it out and help you better?

[sunny_talwar]

Or may be try like this

Aggr(

Sum(TOTAL <CUSTOMER_NAME, QUARTER>Aggr($(vAverageBruttoByCustomer), CUSTOMER_NAME, PLACE, QUARTER)), CUSTOMER_NAME, PLACE, YEAR, QUARTER)

[sunny_talwar · Accepted solution]

‌My bad... i forgot to add the rangesum above part... try this

Aggr(RangeSum(Above(

Sum(TOTAL <CUSTOMER_NAME, QUARTER>Aggr($(vAverageBruttoByCustomer), CUSTOMER_NAME, PLACE, QUARTER)), 0, RowNo())), CUSTOMER_NAME, PLACE, YEAR, QUARTER)

[Anonymous]

You are great!!! That is the right solution!!!

Thanks a lot!!!

[sunny_talwar]

This might also work

RangeSum(Above(Column(1), 0, (Right(QUARTER, 2)/3)))