topic Accumulation in Bar Chart & using Total in QlikView

Accumulation in Bar Chart & using Total

Mon, 11 Jan 2010 14:13:18 GMT

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

Accumulation in Bar Chart & using Total

Mon, 11 Jan 2010 17:56:09 GMT

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)

Accumulation in Bar Chart & using Total

Tue, 12 Jan 2010 08:44:07 GMT

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.

Accumulation in Bar Chart & using Total

Mon, 08 Feb 2010 12:19:42 GMT

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

Accumulation in Bar Chart & using Total

Mon, 08 Feb 2010 12:48:59 GMT

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.

Accumulation in Bar Chart & using Total

Mon, 08 Feb 2010 12:57:22 GMT

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.

Accumulation in Bar Chart & using Total

Mon, 08 Feb 2010 13:33:51 GMT

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!

Accumulation in Bar Chart & using Total

johnw — Thu, 11 Feb 2010 02:11:47 GMT

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.

Accumulation in Bar Chart & using Total

Thu, 11 Feb 2010 10:08:25 GMT

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

Accumulation in Bar Chart & using Total

johnw — Thu, 11 Feb 2010 19:06:02 GMT

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.