Sum data while ignoring current selections.

[#1]

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

[#2]

Hi,   Why don't you try slider objects, like it the file attached. This will allow the end user the flexibility to chose what they want to see.   Regards  Rahul

[#3]

That is indeed a possibility.

I was also wondering if a Macro can be used for this, using a for loop. However I am not sure how to acces the function in the pivot table, showing the result from the function in the pivot table, it somehow gives not the answer.

Even if I try in the function:

function total(a, b)

total = a + b

end function

and then calling the function 'total' in the expression.

Do you know why it does not return any result?