# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Live chat with experts, bring your API Integration questions. June 15th, 10 AM ET. REGISTER TODAY
cancel
Showing results for
Did you mean:
Contributor II

## Cumulate sumproduct with rangesum over two dimensions

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

1 Solution

Accepted Solutions
MVP

‌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)

5 Replies
MVP

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

MVP

Or may be try like this

Aggr(

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

MVP

‌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)

Contributor II
Author

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

Thanks a lot!!!

MVP

This might also work

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