Excluding fields from a set analysis

[Frank_the_Qlikker]

Hi everyone,

I have a situation where I don't want my set analysis to be affected by user-selections, except by one specific field.

Normally all user selections can be disabled by starting the set-analysis with  1:

count({1<ProcesCode={'ED'}>} Score)

But now I want to disable all user selections, except the selection on the field [Schoolyear]

A way to achieve this is the next formula:

count({1<ProcesCode={'ED'}, Schoolyear={$(=chr(39) & replace(GetFieldSelections(Schoolyear,';'), ';', chr(39) & ',' & chr(39)) &chr(39))}>} Score)

This formula retrieves the selected values of the field {Schoolyear], separated by a semicolon (;)  

For example 16-17;17-18;18-19

Since [Schoolyear]  is a text-field, I replaced all semicolons by single quotes and comma-separators, and place additional leading and trailing single quotes

so 16-17;17-18;18-19  becomes  '16-17', '17-18', '18-19'

This string can be used in the set-analysis (which does not react on any user selection). Now it only is affected by selection on [Schoolyear]. So, it works! But....... the formula looks really awful (especially when 2 or more field are to be excluded from the set-analysis). I cannot imagine there's no easier an better way to get the same result.

Can anyone help me please?

[zhadrakas · Accepted solution]

you could try it this way:

count({1<ProcesCode={"ED"}, Schoolyear=P(Schoolyear)>} Score)