RangeSum and Line Chart with 2 dimensions

[ninnartx]

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

[sunny_talwar]

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

[ninnartx]

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?

[sunny_talwar · Accepted solution]

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)

or

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

[ninnartx]

got it. Thank you so much Sunny!

[sunny_talwar]

Which of the two worked for you?

[ninnartx]

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

[sunny_talwar]

Super, thanks for sharing it with us...