Accumulation in Bar Chart & using Total

[#1]

Hi there,

I have created a stacked bar chart where I want to show what the share of products sold is each month.
This works well when doing it for discrete months (eg. in month 1 40% of all volumes sold was Product A & 60% Product B) using 'total <month>'.

Now I would like to do the same thing but using full acumulation so that the chart displays in month 2 what the share was in month 1+2 accumulated. This sounds easy, but I just can't get my head around it...

Any help much appreciated! (It's the chart on the bottom in the file).

Thanks.

Lukas

[#2]

Looks to me like it works like it should ...

Each percentage is calculated agains the cumulative total.

I changed the percentages back to actual numbers and than you see it works (as far as I can tell)

[#3]

Thanks Mark,

the chart would work for me if I were looking for the percentage of products sold over the total period of time.
What I'm looking for though is a chart that shows the cumulated share... below maybe a better example to illustrate:

The cells highlighted in yellow and the chart below is what I'm trying to achieve in QV.

If we look at month 2 of the chart it reads as follows: Of all products sold in month1 and month 2 25% of the quantities have been product A and 75% product B.

[#4]

I had a similar problem, which could be solved by using:

rangesum(above(sum(x),0,rowno(TOTAL)))/rangesum(above(sum(y),0,rowno(TOTAL)))

In your case should be something like:

A: rangesum(above(sum(A),0,rowno(TOTAL))) / (rangesum(above(sum(A),0,rowno(TOTAL))) + rangesum(above(sum(B),0,rowno(TOTAL))) )

B: rangesum(above(sum(B),0,rowno(TOTAL))) / rangesum(above(sum(A),0,rowno(TOTAL))) + rangesum(above(sum(B),0,rowno(TOTAL))) )

[#5]

Thanks, this is heading into the direction I'm currently on. I managed to get it to work with a Pivot Table last week.

rangesum(before(SUM(QV_QTY),0,ColumnNo()))
/
rangesum(before(SUM(TOTAL <LWEEK>QV_QTY),0,ColumnNo()))

QV_QTY is the expression, LWEEK the time dimension.

Unfortunately this code does not work with a bar chart... I'm thinking of the moment if set analysis could be of help here.

[#6]

I used the expression on a line-chart, which worked for me. A line/bar-chart is nothing more than a straight table, so when is works on a straight table, it will work for the chart as well.

[#7]

I think the issue is the dimensionality - with one dimension (time) it works, with two dimensions (time, product) it doesn't.

I can get it to work with two dimensions in a pivot or straight table, but changing the chart type to bar or line chart also changes the sorting order and I can't get it to show correctly.

I've attached an example where it works in a straight table.. maybe I'm just missing something, if you could get the data from the straight table to show in a stacked bar chart it would make my Monday great... 🙂

Thanks!

[johnw · Accepted solution]

Well, I poked at it a while and failed. If your data set is smallish, it could probably be solved with a fake month disconnected from your actual data. Then:

sum( if(Month<=FakeMonth,Quantity))
/sum(total if(Month<=FakeMonth,Quantity))

Or something like that. I can't test it because I can't run your script because you used an Excel spreadsheet instead of inline loads.

[#9]

Thanks John, what an excellent idea to use a disconnected data set!

The formula that does the job finally is:

sum(if(FakeMonth>=Month,Quantity)) /
sum(TOTAL <FakeMonth> if(FakeMonth>=Month,Quantity))

(That's actually even less complicated than using above() etc.)

Please see the solution below if you're interested (with inline data)

Cheers, Lukas

[johnw]

Cool, glad it worked. Disconnected data sets are a very common solution in QlikView. They can cause performance problems when you have a lot of data, and selections can be a little strange since sometimes you're selecting a fake field, and sometimes you're selecting a real field, so it doesn't always behave as intended. But it's a cheap and easy solution, so it tends to get used.

Anyway, if it scales up to your real application, I'd say you're good. If you find it isn't working well enough for you, I'd suggest posting again, and we'll try to find a higher-performance and/or more robust approach.