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: 
crichter14
Creator
Creator

Set Analysis Including Specific Filters only

So I have a set analysis expression:

 

SUM({< [TRANSACTION TYPE CODE]={'INV','SIV'},
[B/S TYPE]={'B','S'},
[LABEL CODE]-={'SV'},
[PRODUCT_GROUP]={'G1'},
[SO HDR GL DATE]={">=$(vMinCurrentDateSelection)<=$(vMaxCurrentDateSelection)"}>}
[LINE QTY])

The user wants the expression to stay static UNLESS they filter on

1.  their Date Group (think Last 30 Days, Last 60 Days, etc)

2.  they filter on Date (think Month Year) 

3.  they filter on the Country (US, UK, etc)

4.  they filter on the Customer (ABC, BAC, CBA)

I've found a ton of discussion on how to exclude things...but is there a way to INCLUDE based on filter, and ignore everything else?

Thank you in advance.

 

2 Solutions

Accepted Solutions
Lauri
Specialist
Specialist

It definitely works in Sense. You need to move your specified field selections out of the Concat, like this:

sum({$<[$(=Concat({1<$Field-={'DATE GROUP','CUSTOMER','COMPANY'}>}distinct $Field,']=,[')&']='),
[TRANSACTION TYPE CODE]={'INV','SIV'},
[B/S]={'B','S'},
[LABEL CODE]-={'SV'},
[PRODUCT GROUP]={'G1'},
[SO HDR GL DATE]={">=$(vMinCurrentDateSelection)<=$(vMaxCurrentDateSelection)">} [QTY])

I didn't test this out but hopefully this is (close to) correct!

View solution in original post

mjtaft2017
Partner - Creator
Partner - Creator


@crichter14 wrote:
Thank you again! I'm still trying to understand what the ,[')&'] portion of this code does, but this is much closer. For some reason I don't need to add Customer to my fields, but I think that is a build issue not a formula one.

That's what makes this  

     sum({$<[$(=Concat({1<$Field-={'DATE GROUP','CUSTOMER','COMPANY'}>}distinct $Field, ']=, [') &']= '),

expand to the parts in red.  The part in green precedes your concat statement

    sum({$<[Date Group]=,[Customer]=,[Company]=,

[TRANSACTION TYPE CODE]={'INV','SIV'},
[B/S]={'B','S'},
[LABEL CODE]-={'SV'},
[PRODUCT GROUP]={'G1'},
[SO HDR GL DATE]={">=$(vMinCurrentDateSelection)<=$(vMaxCurrentDateSelection)">} [LINE QTY])

hope that makes sense

 

View solution in original post

6 Replies
Lauri
Specialist
Specialist

If I understand your requirement correctly, this should be the solution:

https://community.qlik.com/t5/QlikView-Documents/Ignore-all-selections-except-some-specific-fields-u...

crichter14
Creator
Creator
Author

Thank you, Lauischarf.

I have done some tinkering on this but the results are not as required.

Here's the formula I have now:

sum({$<[$(=Concat({1<$Field-={'DATE GROUP','CUSTOMER','COMPANY'},
[TRANSACTION TYPE CODE]={'INV','SIV'},
[B/S]={'B','S'},
[LABEL CODE]-={'SV'},
[PRODUCT GROUP]={'G1'},
[SO HDR GL DATE]={">=$(vMinCurrentDateSelection)<=$(vMaxCurrentDateSelection)"}
>}distinct $Field,']=,[')&']=')>} [QTY])

 

Our QTY went from 308,251 to 1,689,826.  It should stay at 308,251.  Is it possible that post which was written for QlikView, has a different result in Qlik Sense?  (I don't think so, but I need to ask)  Or am I missing some punctuation somewhere?

Has anyone done this in Qlik Sense and gotten it to work?

Cheryl

 

 

 

Lauri
Specialist
Specialist

It definitely works in Sense. You need to move your specified field selections out of the Concat, like this:

sum({$<[$(=Concat({1<$Field-={'DATE GROUP','CUSTOMER','COMPANY'}>}distinct $Field,']=,[')&']='),
[TRANSACTION TYPE CODE]={'INV','SIV'},
[B/S]={'B','S'},
[LABEL CODE]-={'SV'},
[PRODUCT GROUP]={'G1'},
[SO HDR GL DATE]={">=$(vMinCurrentDateSelection)<=$(vMaxCurrentDateSelection)">} [QTY])

I didn't test this out but hopefully this is (close to) correct!

crichter14
Creator
Creator
Author

Thank you again! I'm still trying to understand what the ,[')&'] portion of this code does, but this is much closer. For some reason I don't need to add Customer to my fields, but I think that is a build issue not a formula one.
mjtaft2017
Partner - Creator
Partner - Creator


@crichter14 wrote:
Thank you again! I'm still trying to understand what the ,[')&'] portion of this code does, but this is much closer. For some reason I don't need to add Customer to my fields, but I think that is a build issue not a formula one.

That's what makes this  

     sum({$<[$(=Concat({1<$Field-={'DATE GROUP','CUSTOMER','COMPANY'}>}distinct $Field, ']=, [') &']= '),

expand to the parts in red.  The part in green precedes your concat statement

    sum({$<[Date Group]=,[Customer]=,[Company]=,

[TRANSACTION TYPE CODE]={'INV','SIV'},
[B/S]={'B','S'},
[LABEL CODE]-={'SV'},
[PRODUCT GROUP]={'G1'},
[SO HDR GL DATE]={">=$(vMinCurrentDateSelection)<=$(vMaxCurrentDateSelection)">} [LINE QTY])

hope that makes sense

 

crichter14
Creator
Creator
Author

it does!  I can't thank you enough.  Cheryl