topic Re: Accumulation sum in pivot table in QlikView

Accumulation sum in pivot table

Wed, 10 Dec 2014 09:09:38 GMT

Hello, I have a following data

LOAD * INLINE [

Year, Country, Sales

2010, USA, 6013

2011, USA, 5295

2012, USA, 5551

2013, USA, 6932

2010, UK, 4512

2011, UK, 3976

2012, UK, 4691

2013, UK, 5276

2010, Japan, 2765

2011, Japan, 2567

2012, Japan, 3111

2013, Japan, 3234

2010, Germany, 4374

2011, Germany, 5673

2012, Germany, 4322

2013, Germany, 7654

2010, France, 4965

2011, France, 5097

2012, France, 5419

2013, France, 5732

2010, Australia, 3966

2011, Australia, 4087

2012, Australia, 2376

2013, Australia, 3784

];

If I create a new pivot with Year and Country dimensions

I can easily calculate:

Sales by Country =Sum(Sales)

Total in country dimension =RangeSum(Above(Sum(Sales),0,RowNo()))

And overall Total =RangeSum(Above(TOTAL Sum(Sales), 0, RowNo(TOTAL)))

How can I calculate an overall total by countries? I want full accumulation sum only by countries through the years. This can be easily achieved by swapping dimensions, but this method does not suit me.

Re: Accumulation sum in pivot table

its_anandrjs — Wed, 10 Dec 2014 09:16:32 GMT

Did you try straight chart for this it will give good performance.

Regards

Anand

Re: Accumulation sum in pivot table

mato32188 — Wed, 10 Dec 2014 09:24:34 GMT

Hi Oleg,

not sure, if it fullfill your request...

RangeSum(Above(aggr(Sum(Sales), Country), 0, RowNo(TOTAL)))

Re: Accumulation sum in pivot table

Wed, 10 Dec 2014 09:37:08 GMT

I want to get Line Chart like this

But only way i see is to make an espression for each country.

=RangeSum(Above(Sum({<Country={'Australia'}>}Sales),0,RowNo()))

Re: Accumulation sum in pivot table

mato32188 — Wed, 10 Dec 2014 09:40:03 GMT

In this case, you can use Year, Country as dimensions and sum(sales) as expression, but mark Full Accumulation.

Re: Accumulation sum in pivot table

Wed, 10 Dec 2014 09:49:51 GMT

Yes, it works.

But can i do it without built-in Accumulation?

Re: Accumulation sum in pivot table

rubenmarin — Wed, 10 Dec 2014 10:06:54 GMT

Hi, before I copied the wrong expression, I wanted to to say this:

If(IsNull(Above(TOTAL Acc, Count(DISTINCT TOTAL Country))), 0 , Above(TOTAL Acc, Count(DISTINCT TOTAL Country)))+Sum(Sales)

Acc is the name of the expression

Re: Accumulation sum in pivot table

Wed, 10 Dec 2014 10:23:54 GMT

It works! Now I will try to understand how it works ))). Do you think it is easiest way?

Re: Accumulation sum in pivot table

rubenmarin — Wed, 10 Dec 2014 10:34:56 GMT

Probably there is a better and easier way, but I don't get how to do it right now.

Martin's solution is the easiest way if you can fit that in your requirements.

The expression I suggest will only work if there are the same number countries each year. It takes the last accumulated value 6 rows above (6=Number of countries) and sum the sales of the current row.

The first IsNull is to check if this is the first value for each country, and sum '0' instead of NULL (NULL+Value = NULL, 0+Value=Value)

Re: Accumulation sum in pivot table

Aurelien_Martinez — Wed, 10 Dec 2014 11:05:31 GMT

Hi,

You can use the Alt() function like :

Alt(Above(TOTAL Acc, Count(DISTINCT TOTAL Country)), 0) + Sum(Sales)

Alt : The alt function returns the first of the parameters that has a valid number representation.

Best

Re: Accumulation sum in pivot table

Wed, 10 Dec 2014 15:39:19 GMT

Thank you, it makes the expression much easier.