14 Replies Latest reply: Mar 5, 2013 10:37 AM by andyboyasey RSS

    Accumulation in a pivot table

      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 :

       

      YearMonth# new members
      # members from the start
      2011Dec10 00020 000
      2012Jan5 00025 000

      Feb6 00031 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