Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
tanjalowe
Contributor II
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
sunny_talwar

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

View solution in original post

5 Replies
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

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

View solution in original post

tanjalowe
Contributor II
Contributor II
Author

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