topic Re: Sum data while ignoring current selections. in QlikView

Sum data while ignoring current selections.

Tue, 17 Jul 2012 12:45:38 GMT

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

Re: Sum data while ignoring current selections.

MayilVahanan — Tue, 17 Jul 2012 12:53:34 GMT

Hi,

Try like this,

Edit:

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

Where vEnd = MonthEnd(Max(Date))

vStart =MonthStart(Min(Date))

Hope it helps

Re: Sum data while ignoring current selections.

Tue, 17 Jul 2012 13:12:12 GMT

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.

Re: Sum data while ignoring current selections.

Wed, 18 Jul 2012 07:22:57 GMT

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?

Re: Sum data while ignoring current selections.

Wed, 18 Jul 2012 07:33:57 GMT

TRY:

sum({<Date =>}Amount)

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

Regards Rahul

Re: Sum data while ignoring current selections.

Wed, 18 Jul 2012 07:49:11 GMT

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.

Re: Sum data while ignoring current selections.

Wed, 18 Jul 2012 07:53:01 GMT

if possible could you post an example? Regards R

Re: Sum data while ignoring current selections.

Wed, 18 Jul 2012 08:08:03 GMT

Please see a simple example attached:

Re: Sum data while ignoring current selections.

chris_johnson — Wed, 18 Jul 2012 08:09:46 GMT

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

Re: Sum data while ignoring current selections.

Tue, 24 Jul 2012 07:31:31 GMT

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:

2007: 200

2008: 430

How can I realize this.

Regards.

Re: Sum data while ignoring current selections.

Tue, 24 Jul 2012 08:30:37 GMT

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

Re: Sum data while ignoring current selections.

Wed, 25 Jul 2012 07:36:52 GMT

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?