Accumulation in a pivot table

[mhassinger]

Right now I have a pivot table that looks like this

                    Jan 2014                                             Feb 2014                                             Mar 2014

                    Total Users | Migrated | % Migrated   Total Users | Migrated | % Migrated    Total Users | Migrated | % Migrated

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!

[sunny_talwar]

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,

S

[rwunderlich]

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

[jonasheisterkam]

You can use Valuelist() to disconnect your dimension.

[terezagr]

Hi Michael,

I agree with Rob. Please see attached example.

T.

[mhassinger]

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.

[rwunderlich]

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

[mhassinger]

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!