topic Re: Cumulate sumproduct with rangesum over two dimensions in QlikView

Cumulate sumproduct with rangesum over two dimensions

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Cumulate sumproduct with rangesum over two dimensions

sunny_talwar — Tue, 08 May 2018 09:00:06 GMT

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

Re: Cumulate sumproduct with rangesum over two dimensions

sunny_talwar — Tue, 08 May 2018 09:08:40 GMT

Or may be try like this

Aggr(

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

Re: Cumulate sumproduct with rangesum over two dimensions

sunny_talwar — Tue, 08 May 2018 09:57:33 GMT

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

Re: Cumulate sumproduct with rangesum over two dimensions

Anonymous — Tue, 08 May 2018 10:03:11 GMT

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

Thanks a lot!!!

Re: Cumulate sumproduct with rangesum over two dimensions

sunny_talwar — Tue, 08 May 2018 11:06:12 GMT

This might also work

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