
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
Accepted Solutions


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
If I understand your requirement correctly, this should be the solution:

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
it does! I can't thank you enough. Cheryl
