Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
MayilVahanan

Hi,

    

     Try like this,

     

     Edit:

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

     Where vEnd = MonthEnd(Max(Date))

               vStart =MonthStart(Min(Date))

     Hope it helps

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
Not applicable
Author

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
Author

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
Author

TRY: 

sum({<Date =>}Amount) 

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

Regards  Rahul

Not applicable
Author

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
Author

if possible could you post an example?   Regards  R

Not applicable
Author

Please see a simple example attached:

chris_johnson
Creator III
Creator III

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
Author

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.