Qlik Community

Qlik Sense Advanced Authoring

Discussion board where members can learn more about Qlik Sense Advanced Authoring.

cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
Creator III
Creator III

Re: Set Analysis Including Specific Filters only

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

Highlighted
Partner
Partner

Re: Set Analysis Including Specific Filters only


@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
Highlighted
Creator III
Creator III

Re: Set Analysis Including Specific Filters only

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...

Highlighted
Creator
Creator

Re: Set Analysis Including Specific Filters only

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

 

 

 

Highlighted
Creator III
Creator III

Re: Set Analysis Including Specific Filters only

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

Highlighted
Creator
Creator

Re: Set Analysis Including Specific Filters only

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.
Highlighted
Partner
Partner

Re: Set Analysis Including Specific Filters only


@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

Highlighted
Creator
Creator

Re: Set Analysis Including Specific Filters only

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