Accumulation in a pivot table

[#1]

Hi everyone,

I am facing some problems to display an accumulated amount in a pivot table. Currently, I am working on members registration (with a registration date) and i want a pivot table looking like this :

Year	Month	# new members	# members from the start
2011	Dec	10 000	20 000
2012	Jan	5 000	25 000
	Feb	6 000	31 000

Assuming that the first members registred on November 2011, the previous table is showing data filtered by an active selection (from Dec 2011 to Feb 2012).

My members table looks like this (without a few dimensions such as language_id, channel_id..) :

• member_id
• member_registration_date
• member_count // always equal to "1"

Getting the first expression # new members is quite simple : SUM(customer_count)

Now, I want, in the second expression, the # of members from the start (ie. Nov 2011) to the end of the month. Indeed, the first line of my table (Dec 2011) should be : # new members in Nov 2011 (10.000) + # new members in Dec 2011 (10.000) = 20.000

However I tried many set analysis and expressions, I couldn't find a way to do this...

Thanks in advance for your help,

Damien

[#2]

Hi all,

Thanks everyone for your time and your answers.

Celambarasan, it helps a lot !

It works perfectly fine on a classic pivot table. However, I dragged the dimensions to set them vertically, and the expressions to set them horizontally, and the above() function doesn't seem to work very well (null value)

Any ideas ?

Regards,

Damien

[jagan]

Hi,

Instead of Above() use Before().

Regards,

jagan.

[CELAMBARASAN]

Hi,

     For horizontal calculation you have to use Before instead of Above.

Celambarasan

[#5]

Guys ! You're all great !

To sum up everything :

I used a variable :

vMembersAccumulation = Sum({1<member_registration_date={"<$(=Min(member_registration_date))"}>}  member_count)

The expression is (with vertical dimensions and horizontal expressions) :

[# members] = SUM(member_count) + ALT(Before(TOTAL [# members]), vMembersAccumulation)

Thanks a lot,

Regards,

Damien

[#6]

Thank you so much Jagan Mohan

jagan mohan wrote:

Hi,

Try the following expression

=RangeSum(Above(TOTAL Sum(NewMembers), 0, Rowno(TOTAL)))

Regards,

Jagan

That does the trick of calculating running totals for any Journal regardless of the dimensions - just need a straight table and this calculated expression.