topic Re: Accumulation in a pivot table in QlikView

Accumulation in a pivot table

mhassinger — Mon, 19 Jan 2015 20:18:31 GMT

Right now I have a pivot table that looks like this

Jan 2014 Feb 2014 Mar 2014

USA
UK

Germany
France

So 2 dimensions (MonthYear and Country) and 3 expressions (Total Users, Migrated, and % Migrated)

Total Users = Sum(TotalUsers)

Migrated = Count({<MigrationStatus={'Migration Completed'}>}UserName)

% Migrated = Migrated / Total Users

What I need to be able to do is have the Migrated expression become cumulative, so that if if USA migrated 100 users in Jan and 200 in Feb, the value in Feb should be 300.

How would I go about re-writing the expression? Thanks!

Re: Accumulation in a pivot table

sunny_talwar — Mon, 19 Jan 2015 20:28:06 GMT

I would try to do the cumulative count at the script level itself. I can be more helpful if you have a sample qvw file.

Best,

Re: Accumulation in a pivot table

rwunderlich — Mon, 19 Jan 2015 20:42:50 GMT

You can make your pivot table expressions cumulative by wrapping them in a rangesum(before()) function.

rangesum(before(Sum(TotalUsers),0,RowNo()))

-Rob

http://masterssummit.com

http://robwunderlich.com

Re: Accumulation in a pivot table

jonasheisterkam — Mon, 19 Jan 2015 20:51:29 GMT

You can use Valuelist() to disconnect your dimension.

Re: Accumulation in a pivot table

terezagr — Mon, 19 Jan 2015 21:02:58 GMT

Hi Michael,

I agree with Rob. Please see attached example.

Re: Accumulation in a pivot table

mhassinger — Tue, 20 Jan 2015 19:48:33 GMT

I'm attaching my own QV file to see if anyone can see what I'm doing wrong. If you look at the "Migrated" expression, you'll see that I'm trying to use the rangesum and before functions, but it isn't producing the expected result.

Re: Accumulation in a pivot table

rwunderlich — Tue, 20 Jan 2015 19:59:14 GMT

My mistake. The rowno() won't always work properly as a counter. Use 999 instead as the row counter.

rangesum(before(Count({<MigrationStatus={'Migration Completed'}>}DisplayName),0,999))

Make 999 bigger if you have more date than that

-Rob

http://masterssummit.com

http://robwunderlich.com

Re: Accumulation in a pivot table

mhassinger — Sun, 25 Jan 2015 20:06:53 GMT

Thanks all for the help. I couldn't get the rangesum solution to work in the chart for some reason, so I just added a cumulative column to the script and used rangesum and peek to built it out there. Thanks again for all the suggestions!