Ignore all selections except some specific fields using Set Analysis

[#1]

Hello.

As far as I was designing apps for QV - I've found a great need in some charts to ignore all possible selections except some specific fields.

Exploring possible ways to do so - the first my decision was to use dollar-sign expression inside my Set Analysis which could make an exception like next:
(Let's imagine that in my scheme there are fields [Stock], [Manager], [Date], [City], [Client] and [Amount])

sum({1<[Stock]={'Stock 1','Stock 3'}>} Amount)

If I selected only 'Stock 1' and 'Stock 3' in field [Stock] - and this way has shown it's profit - you can get it using this expression:

sum({1<[Stock]=$(=if(GetSelectedCount([Stock], true( ))>0,'{' & chr(39) & GetFieldSelections([Stock],chr(39) & ',' & chr(39),GetSelectedCount([Stock], true( ))) & chr(39) & '}', ''))>} Amount)

If you need two, three, or more fields - you can simply add such code for every field into Set Analysis:

sum({1<[Stock]=$(=if(GetSelectedCount([Stock], true( ))>0,'{' & chr(39) & GetFieldSelections([Stock],chr(39) & ',' & chr(39),GetSelectedCount([Stock], true( ))) & chr(39) & '}', '')),[Manager]=$(=if(GetSelectedCount([Manager], true( ))>0,'{' & chr(39) & GetFieldSelections([Manager],chr(39) & ',' & chr(39),GetSelectedCount([Manager], true( ))) & chr(39) & '}', ''))>} Amount)

But what if you have a field containing hundreds of records? Can you imagine what will be the length of your expression? That's why I started to discover another way to do that. I've thought - what if I use not "1" Set, but "$" Set in my expression and write in it all the fields that must be ignored, something like that:

sum({$<[Date]=,[City]=,[Client]=,[Amount]=>} Amount)

So, using such expression - I get that all the fields except for [Stock] and [Manager] are ignored, but how car I make it automatic? Sure using our lovely dollar-sign expressions and system field $Field:

sum({$<[$(=Concat({1<$Field-={'Stock','Manager'}>}distinct $Field,']=,[')&']=')>} Amount)

where inside the set analysis of Concat function you can insert fields that you are interested in (by the way - there you can insert even tables using system field $Table and others).

[Anonymous]

Thank you for sharing.

I used your solution in order to take in account :

• the selections made by the user in only one specific field (FIELDNAME1)
• only one specific value from another field (FIELDNAME2)

Sum({$<[$(=Concat({1<$Field-={'FIELDNAME1'}>}distinct $Field,']=,[')&']='), FIELDNAME2={'FIELDVALUE'}>} AMOUNT)

[hewemel1]

Thank you for sharing. Very helpful.

Is it possible to define a macro for this code, in order to make it easier to use it in expressions in the GUI?

I tried it with

LET IgnoreSelectionsExeptOfSomeFields='[$' & '(=' & 'Concat({1<$' & 'Field-={' & chr(39) & '$(1)' & chr(39) & '}>}distinct $' & 'Field,' & chr(39) & ']=,[' & chr(39) & ' )&' & chr(39) & ']=' & chr(39) & ')';

The result looks promising in the variable list, but I could not call the macro in a way that makes it work.

[crossroadsit]

This should get some kind of award.

[crossroadsit]

Actually, better than an award, I wonder if Qlik would consider adding this as a function built into the set analysis syntax. I'm pretty new to set analysis but I was almost immediately looking for a way to achieve this behavior. It's super useful so it would be nice if it could be invoked more easily.

[Anonymous]

Hi,

I am new to Qlikview and have just a query regarding this post i.e., whether concat function affects the Performance if we have more number of fields.

Regards,

MK

[#7]

Hello, everyone.

Prior to June 2017 Qlik Sense version I was using Denys Smakovskyi's expression

sum({$<[$(=Concat({1<$Field-={'Stock','Manager'}>}distinct $Field,']=,[')&']=')>} Amount)

as a basis for ignoring all selections with exception of some specific fields and it worked perfectly.

But, after I have installed June 2017 version, instead of result my KPI field started showing "-".

Does anyone know is something happened to the expression recognition rules in the June 2017 version or is there another issue?

Respectfully

Lev

[crossroadsit]

I have the same issue as Lev. I.e. that this technique seems to fail on the June 2017 version of Qlik Sense.
Will be tragic if it can't be done anymore... such a useful technique

[caio_caminoski]

I have the same problem!  =(

[#10]

Dear,

I've Qlik Sense June 2017 and with a little modification to the code it seems to work. Here is my expression:

=Sum({$<$(= '[' & Concat({1<$Field-={'Your field num1', 'Your field num2'}>} $Field,']=,[') & ']=')>} [other field])

The trick is to modify the original removing [ before the second $ and putting inside the expression:

sum({$<[$(=Concat({1<$Field-={'Stock','Manager'}>}distinct $Field,']=,[')&']=')>} Amount)

Hope it helps.

[#11]

Gabriele,

Thank you for your help.

I don't know about June 2017, but in September 2017 your formula works.

Also, starting from September 2017 version, the old formula works again!