Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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

11 Replies

Re: Sum data while ignoring current selections.

Hi,

    

     Try like this,

     

     Edit:

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

     Where vEnd = MonthEnd(Max(Date))

               vStart =MonthStart(Min(Date))

     Hope it helps

Not applicable

Re: Sum data while ignoring current selections.

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.

Not applicable

Re: Sum data while ignoring current selections.

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?

Not applicable

Re: Sum data while ignoring current selections.

TRY: 

sum({<Date =>}Amount) 

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

Regards  Rahul

Not applicable

Re: Sum data while ignoring current selections.

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.

Not applicable

Re: Sum data while ignoring current selections.

if possible could you post an example?   Regards  R

Not applicable

Re: Sum data while ignoring current selections.

Please see a simple example attached:

chris_johnson
Contributor II

Re: Sum data while ignoring current selections.

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

Not applicable

Re: Sum data while ignoring current selections.

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.

Community Browser