topic Visualise the accumulation in a chart bar in QlikView

Visualise the accumulation in a chart bar

Tue, 18 Oct 2011 10:40:00 GMT

Hello,

I'm using a bar chart : is there any way to make the accumulation visible ?

This is what I want : per year, I need to count a number of products.

2008 : 1

2009 : 2

2010 : 3

That's great. I select the "Full accumulation", and it gives me :

2008 : 1

2009 : 3

2010 : 6

OK, but the bars are all in the same color, and I can't tell wich is the number of products of the year and which is the accumated part...

Don't know if I'm clear...

I would like to have one portion for the numbert of products of the year and another portion, in another color, for the accumulated products of the previous years (of course, stacked).

Is there any way to do that ?

Thanks,

Fred

Visualise the accumulation in a chart bar

Miguel_Angel_Baeyens — Tue, 18 Oct 2011 10:40:00 GMT

Hi Fred,

Use the same expression twice. In the first one set it to not accumulate so you will see each year with their products. In the second expression set it to full accumulation, so you see this year and all the previous summed up.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Visualise the accumulation in a chart bar

Tue, 18 Oct 2011 11:06:31 GMT

Hi Miguel,

Thanks for your answer.

I have modify the expressions according to your advice and this is what I get :

That's a good start but the result is not correct. Blue part is OK, but the red one (the accumlated) is not good because it includes not only the total of products of the previous years but also the total of the year.

I will try and search how to solve this.

If you have an idea, I'm all ears (or eyes) !

Fred

Re: Visualise the accumulation in a chart bar

Miguel_Angel_Baeyens — Tue, 18 Oct 2011 11:11:00 GMT

Hi Fred,

That is exactly what Full Accumulation does: sum up the current expression value to all the previous values. You can use the RangeSum() function along with Before() or similar to sum up all previous values except for this year. Check this post in this regard.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

EDIT: Example file updated using the following expression to accumulate except for this year

RangeSum(Above(Sum(Amount), 1, RowNo()))

Visualise the accumulation in a chart bar

Tue, 18 Oct 2011 12:19:36 GMT

Hi Miguel,

As I'm new to QlikView, I will need some time to adapt your example to my case.

I will let you know when I succeed !

Thanks again,

Fred

Visualise the accumulation in a chart bar

Tue, 18 Oct 2011 12:23:32 GMT

You are really great !

Thank you very much for your example, seems to work fine for me !

(I hope one day I will be as great as you... I reckon it will take some time )

Fred