Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
The #1 reason QlikView customers adopt Qlik Sense is a desire for a modern BI experience. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
MVP
MVP


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.

Highlighted
Not applicable

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)



Highlighted
MVP
MVP


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

Highlighted
Not applicable

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

Highlighted
Not applicable

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...
Highlighted
Employee
Employee

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 &']=', ', [')

Highlighted
Contributor II
Contributor II

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

Highlighted
Contributor
Contributor

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)

Highlighted
Master II
Master II

Maybe like that:

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

Highlighted
Partner
Partner

Hi Eran,

Could you try this:

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