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

[MayilVahanan]

Hi,

     Try like this,

     Edit:

   =Sum({<Date = {'>=$(=vStart)<=$(=vEnd)'}>}Sales)

     Where vEnd = MonthEnd(Max(Date))

               vStart =MonthStart(Min(Date))

     Hope it helps

[#3]

Hi,

That produces the same answer as what I tried:

sum({$<date = {"<=$(<=max(date))"}>} Amount).

Quotes or <= changing did not work either.

Is there another solution, maybe? or is the only way to design a new table with these values, to sum up.

[#4]

The new solution produces also the same result.

Because I have selected the year 2008: the cube has been sliced and diced only for 2008. So if I pick the max and min date from the whole set it will only look at the year 2008 (more specific at month January).

But what I want is even if I selected the year 2008 the sum has to be over all the years. So that it takes not slicing and dicing into account. Is it therefore possible to add a kind of constraint to not look at the selections that are made?

[#5]

TRY: 

sum({<Date =>}Amount) 

This should give you total amount purchased irrespective of your selection in date.  

Regards  Rahul

[#6]

That still produces the same result.

Its like, when I have selected the year 2008, as current selection, this data is the only part that is cached and the rest is not available and therefore is not used.

[#7]

if possible could you post an example?   Regards  R

[#8]

Please see a simple example attached:

[chris_johnson]

Hi,

If you want to ignore the Year then add "Year=" into your set analysis, so something along the lines of:

({$<date = {"<=$(<=max(date))"}, Year=>} Amount)

By specifying a blank expression the other side of the field, you are ignoring any selections on that field. The above should ignore the Year selection. I imagine you may also want to do the same on some of the other date fields.

Chris

[#10]

Dear Lakhina007,

I have tested your example. Now to make clear what I exactly want (using your example) is.

Use: customer A3, which has values in 2007 and 2008.

Now selecting 2007 it has to show 200 which it does. Good, now I select 2008 and what I want to see then is:

The sum of the values of all previous year including the selected one. So for example, if I select only 2008 and the month Feb-2008.

Then I want to see the value of (Feb-2008 + values of 2007) which should show the value 230 + 200 = 430.

Now if I select the years: 2007 and 2008 (only the month Feb-2008). Then I want to see is:

           A3

2007: 200

2008: 430

How can I realize this.

Regards.