11 Replies Latest reply: Jul 25, 2012 3:36 AM by Yme Joustra RSS

    Sum data while ignoring current selections.

      I am currently busy building a pivot table, with years, months and customers.

       

      Over the years customers have bought stuff.

       

      Now what I want to see is the total amount the customer bought from his beginDate to a selected month in a selected year.

       

      So for example: customer1: beginDate: 2007-01-01 bought over the years four things of

      $1 in 2007-02-01

      $2 in 2007-07-01

      $3 in 2008-01-01

      $4 in 2009-01-01

       

      Now I select the year 2008 and I want to see what he has bought in January (so 2008-01-01) so far.

       

      And what I want to see is:

      Year: Month:          Amount:

      2008: 2008-01-01: $6

       

      But when I execute the process I get only to see :

      Year: Month:          Amount:

      2008: 2008-01-01: $3

       

      Because in current  selections 2008 is selected I get only from this year and month the data, while I want everyting that is smaller than this date (without is having selected). Is there something to ignore in this case the current selection.

       

      I tried already something like this:

      sum({$<date = {"<=$(<=max(date))"}>} Amount)  however it picks only over 2008 and not the years and months before.

       

      I hope someone can help me with this.


      Best regards