Qlik Community

Qlik Sense Advanced Authoring

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

Highlighted
crichter14
New Contributor III

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
laurischarf
Contributor 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!

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

 

6 Replies
laurischarf
Contributor 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...

crichter14
New Contributor III

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

 

 

 

laurischarf
Contributor 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!

crichter14
New Contributor III

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

 

crichter14
New Contributor III

Re: Set Analysis Including Specific Filters only

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