topic Re: RangeSum and Line Chart with 2 dimensions in QlikView

RangeSum and Line Chart with 2 dimensions

ninnartx — Tue, 28 Mar 2017 08:23:12 GMT

Hi everyone,

Would really appreciate some help.
I'm trying to calculate the cumulative % Contribution for each product by month.

Product A in January = sales of Product A in Jan / total sales in Jan

Product A in February = sales of Product A in Jan + Feb / total sales in Jan + Feb

Product A in March = sales of Product A in Jan + Feb + Mar / total sales in Jan + Feb + Mar

and so on...

Checking the the cumulative box does not work in this case because Qlikview will add the percentages up.

So I used RangeSum and am still not getting the desired values. As you can see in the image below, the total for each month does not add up to 100% (but it should!).

I can't seem to figure it out. Any help would be appreciated! I've attached a qvw file to this post as well.

Thank you !!!!!

Re: RangeSum and Line Chart with 2 dimensions

sunny_talwar — Tue, 28 Mar 2017 08:51:47 GMT

Not 100% sure, but may be this

=RangeSum(Above(Sum([Sales by Month]), 0, RowNo()))/Sum(TOTAL <Month> Aggr(RangeSum(Above(Sum([Sales by Month]), 0, RowNo())), Month, Product))

Re: RangeSum and Line Chart with 2 dimensions

ninnartx — Tue, 28 Mar 2017 09:29:28 GMT

Hi Sunny,

Thank you so much for your response! But sadly it is still not what I'm trying to accomplish.

From your formula, I separated the numerator and denominator into 2 columns. If you look at the numerator column, you'll notice that the formula adds all the products within that month.

What it's doing:

Product A in Jan = sales of Product A in Jan

Product B in Jan = sales of Product A + B in Jan

Product A in Feb = sales of Product A in Feb

Product B in Feb = sales of Product A + B in Feb

What I'm looking for:

Product A in Jan = sales of Product A in Jan

Product B in Jan = sales of Product B in Jan

Product A in Feb = sales of Product A in Jan + Feb

Product B in Feb = sales of Product B in Jan + Feb

I feel like that formula is reallllly close to what I'm trying to accomplish though! Maybe just a tiny tweak somewhere?

Re: RangeSum and Line Chart with 2 dimensions

sunny_talwar — Tue, 28 Mar 2017 09:37:12 GMT

Not sure, but may you need one of these two in your denominator?

=RangeSum(Above(Sum([Sales by Month]), 0, RowNo()))/Aggr(RangeSum(Above(Aggr(RangeSum(Above(Sum([Sales by Month]), 0, RowNo())), Month, Product), 0, RowNo())), Product, Month)

Aggr(RangeSum(Above(Sum([Sales by Month]), 0, RowNo())), Product, Month)

Re: RangeSum and Line Chart with 2 dimensions

ninnartx — Tue, 28 Mar 2017 10:03:30 GMT

got it. Thank you so much Sunny!

Re: RangeSum and Line Chart with 2 dimensions

sunny_talwar — Tue, 28 Mar 2017 11:00:48 GMT

Which of the two worked for you?

Re: RangeSum and Line Chart with 2 dimensions

ninnartx — Wed, 29 Mar 2017 02:28:04 GMT

Mixed and matched the formulas you suggested and ended up with:

=Aggr(RangeSum(Above(Sum([Sales by Month]), 0, RowNo())), Product, Month) /

Sum(TOTAL <Month > Aggr(RangeSum(Above(Sum([Sales by Month]), 0, RowNo())), Month))

Re: RangeSum and Line Chart with 2 dimensions

sunny_talwar — Wed, 29 Mar 2017 08:23:57 GMT

Super, thanks for sharing it with us...