Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set Analysis - Ignore all selections except certain field

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?

20 Replies
johnw
Champion III
Champion III


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.

Not applicable
Author

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
Champion III
Champion III


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."

Not applicable
Author

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

Not applicable
Author

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
Not applicable
Author

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
Contributor II
Contributor II

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

Anonymous
Not applicable
Author

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
Master II
Master II

Maybe like that:

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

wilsonwebb
Partner - Contributor III
Partner - Contributor III

Hi Eran,

Could you try this:

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