Set Analysis - Ignore all selections except certain field

[#1]

Hello,

I'm trying to figure out how to calculate the sum of a measurement, disregarding the selections in all the fields except a certain field.

Let's assume the following fields:

Year, Person, Section, Sales. I would like to calculate the Sum of Sales, disregarding the selections at all fields but regarding the selections made at the "Person" field.

The expression:

SUM({1<Person=>}Sales)

Will not due the trick as the result will not change according to the selections made at the "Person" field.

Does anyone as a suggestion?

[johnw]

Smakovskiy wrote:but your ideas do not disregard all the other selections - if any selection in other field can have an influence on the possible values in this field - then your variant will show not the selected positions, but only possible inside the selection.

Understood. If I select F1 = 3 & 4, and F2 = 3, then technically speaking, 4 is still selected in F1, even though it is no longer possible and thus no longer visibly selected. I can't say which approach eran_moses is looking for, though. Off hand, I'd think that if only 3 is visibly selected, I'd want to limit the chart to 3. But if we want 4 as well, your approach does that. On the other hand, you're specifically coding to say that if nothing is selected, show everything. If we assume we're going by selections instead of by what's possible, wouldn't you want to not show anything in that case? That would be this, I think (as long as '-' is not a legitimate value of F1):

sum({1<F1={'$(=getfieldselections(F1,chr(39)&','&chr(39)))'}>}F2)

In any case, I'm not sure we have enough information to declare any of these variants right or wrong. The right version is the one that produces the results the user wants. I am not clear what the user wants.

[#3]

John, I've made a check of this if you'll look carefully:

SUM({1<F1=$(=if(getselectedcount(F1,true( ))>0,'{'&chr(39)&GetFieldSelections(F1,chr(39)&','&chr(39),getselectedcount(F1,true( )))&chr(39)&'}','')) >}F2)

then if nothing is selected - then getselectedcount(F1,true( ))=0 and then whole "if" will return '' and the final function will be:

SUM({1<F1=>}F2)

[johnw]

Smakovskiy wrote:John, I've made a check of this if you'll look carefully:
SUM({1<F1=$(=if(getselectedcount(F1,true( ))>0,'{'&chr(39)&GetFieldSelections(F1,chr(39)&','&chr(39),getselectedcount(F1,true( )))&chr(39)&'}','')) >}F2)
then if nothing is selected - then getselectedcount(F1,true( ))=0 and then whole "if" will return '' and the final function will be:
SUM({1<F1=>}F2)

Yes, I understand that. That's what I meant by "you're specifically coding to say that if nothing is selected, show everything."

[#5]

Is there a reason to not use double quotes " instead of chr(39)?

[#6]

Another way to do this is via a variable. In set analysis you can cancel a selection in a field by typing {$<xxxx_fieldname_xxxx=} So you could type all your fields like this except the field on which you do not want the selections to be cancelled. The problem with this is, that it takes to long + will be tough on maintenance because every time you add a field to a file you will have to type it in the set as well. So why not have QlikView do it for you? Create a variable unselect_all_except and define it as

=

concat ({$<$Field =-{xxxx_fieldname_xxxx, xxxx_fieldname2_xxxx}>}$Field &'=', ', ')

then use this in your set

{$<$(unselect_all_except)>}

and that's that...

[Anonymous]

slight modification to handle fields with spaces. Need to prefix the statement with a [ and then for each concatenation include a closing and opening bracket

='[' & concat({$<$Field =-{[Movement group], [Movement category], [Movement sub-category],  [Movement type], [Action type], [Action reason] }>}$Field &']=', ', [')

[zippo_btl]

Without Set Analysis, but u can make copy of this certain field (data island) while loading data.

[Anonymous]

Another solution we found (credit goes to michealz‌)

Create a variable:

SET vSetAnalysis = =if(GetSelectedCount(Person) > 0, '<Person = P(Person)>', Null()) ;

And use the expression:

Sum({1 $(vSetAnalysis)} Sales)

[Frank_Hartmann]

Maybe like that:

=sum({1<[Person]=$::[Person]>}Sales)

[wilsonwebb]

Hi Eran,

Could you try this:

SUM({1<Person={'*'}>}Sales)