topic Re: Set Analysis Including Specific Filters only in App Development

Set Analysis Including Specific Filters only

crichter14 — Sat, 16 Nov 2024 20:45:10 GMT

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.

Re: Set Analysis Including Specific Filters only

Lauri — Thu, 30 May 2019 22:15:13 GMT

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-using-Set/ta-p/1482031

Re: Set Analysis Including Specific Filters only

crichter14 — Thu, 30 May 2019 22:33:47 GMT

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

Re: Set Analysis Including Specific Filters only

Lauri — Fri, 31 May 2019 01:22:39 GMT

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!

Re: Set Analysis Including Specific Filters only

crichter14 — Fri, 31 May 2019 14:35:51 GMT

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.

Re: Set Analysis Including Specific Filters only

mjtaft2017 — Fri, 07 Jun 2019 17:38:59 GMT

@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

Re: Set Analysis Including Specific Filters only

crichter14 — Wed, 12 Jun 2019 16:15:11 GMT

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