Ignore Filter in Set Modifier

[Daniel29195]

Hello,
i have these 2 following expressions : 

• Measure 1 : 
  Sum( {$  <Channel,   [Invoice Date] ={"=Year([Invoice Date]) = $(=max(year([Invoice Date])-1))"}>}       [# Amount])
  
  Measure 2 :
• Sum({<Channel>}[# Amount])
  
  
  i have w filter pane :  Channel 
  
  
  

as you can see,  in my 2 expressions, im ignoring the filter Channel from filtering my 2 expressions,

it is working foir the scond measure, but not working for the first measure, 

i cant find the problem,

any help would be appreciated . 

[RafaelBarrios]

Hi @Daniel29195 

it’s something weird in the [Invoice Date] evaluation

=Year([Invoice Date]) = $(=max(year([Invoice Date])-1))
Date=year=year ?

I would suggest to create a year field in script [Invoice year]

and then

Sum( {$  <Channel,   [Invoice year] ={$(=year(max())-1)"}>}       [# Amount])

hope this helps.

best,

Help users find answers! Don't forget to mark a solution that worked for you & to smash the like button! 🙂

[Daniel29195]

Hello @RafaelBarrios,
what im using is a search expression, it should work, without the need to create w new field called invoice_year  = year([Invoice Date]), 

i did try your solution, but unfortunatelyit didnt work also.

i managed to create another expression, that did work, : 

sum(
{
< Year,Channel,
[Invoice Date] = {">=$(=date(YearStart(addyears(max([Invoice Date]),-1)),'MM/DD/YYYY')) <=$(=date(yearend(addyears(max([Invoice Date]),-1)),'MM/DD/YYYY'))"}>
}
[# Amount]

)

however i wanted to know why the other one is not working !  and  I couldnt find the reason . it is frustrating 😅

[RafaelBarrios]

Great @Daniel29195 

I’m glad you fixed

mine have some errors, I was missing a double quote and the field 😅

Sum( {$  <Channel,   [Invoice year] ={"$(=year(max([Invoice Date]))-1)"}>}       [# Amount])

I thinks is better but can’t try it now.

i will try your approach latter

best regards,

[Daniel29195]

https://qlikviewcookbook.com/2022/03/expression-as-left-side-of-set-modifier/

Sum( {$<
Channel,
"=Year([Invoice Date])" = {">=$(=year(date(max([Invoice Date])))-1)"}>}[# Amount])

it turns out, that this is the correct way to write it, it works just fine now. 

[RafaelBarrios]

😱 new trick unlocked, never tried that before

im as surprised as Rob commented he was on that article.

thanks for bringing it here.

I would give you the “solved” but I can’t 😅

best,

[AndyC]

I thought that the '=' needs to be added to exclude a filter in set analysis?

i.e.

 {$  <Channel=,   [Invoice year] ={$(=year(max())-1)"}>}  

[Daniel29195]

 you just need to write the name of the field 

Field1, field2, ...

[vinieme12]

Try Adding an EQUAL SIGN

<Year=,Channel=,....

[Oleg_Troyansky · Accepted solution]

Hi Daniel,

I love to see questions with the data examples from my book! You made my day.

While you uncovered many interesting techniques in this thread alone, let me explain to you why the original formula didn't work. Your Set Analysis condition for the Date field looked like this:

 [Invoice Date] ={"=Year([Invoice Date]) = $(=max(year([Invoice Date])-1))"}

The equals sign within the double quotes signifies Advanced Search. It tells Qlik to evaluate the expression after the equals sign at the level of the corresponding field (Invoice Date) and include those field values (dates) that render the condition to be true.

So, for each Invoice Date, you are looking for those dates where the Year(date) = max(Year(date) - 1 ). Without any additional Set Analysis conditions, the Year(date) is always the same as max(year(date)). So, your condition, in a nutshell, is asking for something like this:

2015=2014 for those dates in 2015, or 2014=2013, for those dates in 2014.

Hence, no dates ever satisfy this condition.

If you wanted to use this kind of syntax (not that I recommend it), perhaps using the Set Analysis {1} inside the Max() function could help:
  

 [Invoice Date] ={"=Year([Invoice Date]) = $(=max( {1} year([Invoice Date])-1))"}

For anyone who wants to learn more about advanced Set Analysis, Advanced AGGR(), and many other advanced Qlik Methodologies - join us at the Masters Summit for Qlik in New Orleans on Nov 14-16. You will learn a lot of exciting advanced material!

Cheers,