topic Set Analysis - Ignore all selections except certain field in QlikView

Set Analysis - Ignore all selections except certain field

Thu, 16 Dec 2010 10:35:00 GMT

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?

Set Analysis - Ignore all selections except certain field

Thu, 16 Dec 2010 10:50:53 GMT

Hi,

Try the following:

SUM({$<Year=,Section=,Sales=>} Sales)

Only selections on Person field will be taken.

Regards.

Set Analysis - Ignore all selections except certain field

spsrk_84 — Thu, 16 Dec 2010 10:51:33 GMT

Hi,

Try this way

SUM( {<Year = , Section=>}Sales)

Now it will not respect selections done on Year and section columns...........

I hope this what is required to you if not explain the problem once agin clearly..

Regards,

Ajay

Set Analysis - Ignore all selections except certain field

Thu, 16 Dec 2010 10:59:32 GMT

Hi,

Thanks for the qiuck reply.

This solution works fine, but it's a work-around that might get klutzy in case of many fields to be ignored. Is there any other solution which will require to indicate only the field "Person" in the Set Analysis formula?

Set Analysis - Ignore all selections except certain field

Thu, 16 Dec 2010 11:01:05 GMT

Hi again,

If you have a lot of fields you could try the following:

=Sum({1<Person={$(v_Person)}>} Amount)

Where the expression of the variable is:

=Concat(Distinct chr(39) & Person & chr(39),',')

I hope it helps you.

Regards.

** Corrected, thanks to Smakovskiy

Set Analysis - Ignore all selections except certain field

Thu, 16 Dec 2010 11:04:04 GMT

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

GL 🙂

Set Analysis - Ignore all selections except certain field

Thu, 16 Dec 2010 11:08:23 GMT

Migel, your way is good only if there is numeric field but when field is text, then your way will return null

Set Analysis - Ignore all selections except certain field

Thu, 16 Dec 2010 11:11:38 GMT

Right,

then you will have to use:

=Concat(Distinct chr(39) & Person & chr(39),',')

As well as you wrote in your expression.

Regards

Set Analysis - Ignore all selections except certain field

johnw — Fri, 17 Dec 2010 02:54:47 GMT

Perhaps this. If I got the syntax right, it says to ignore everything but Person, and for Person, use the set of possible Persons. You might need P(Person) to tell it possible persons, but I don't think so. I think it figures out what you mean without that.

sum({1<Person=P()>} Sales)

Set Analysis - Ignore all selections except certain field

Fri, 17 Dec 2010 09:02:47 GMT

John, Migel, your variants are good but not always - I was solving such problem and tried all your variants. Both your ideas are right, 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.

There is a problem of uploading an attachment - I've uploaded it to rapidshare - try it and you'll understand the difference.

http://rapidshare.com/files/437773891/9.qvw

Set Analysis - Ignore all selections except certain field

johnw — Sat, 18 Dec 2010 02:12:43 GMT


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.

Set Analysis - Ignore all selections except certain field

Sun, 19 Dec 2010 01:21:17 GMT

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)

Set Analysis - Ignore all selections except certain field

johnw — Mon, 20 Dec 2010 18:44:07 GMT


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

Set Analysis - Ignore all selections except certain field

Fri, 18 Feb 2011 16:48:19 GMT

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

Set Analysis - Ignore all selections except certain field

Thu, 26 Apr 2012 14:16:23 GMT

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

Re: Set Analysis - Ignore all selections except certain field

Anonymous — Wed, 12 Aug 2015 05:25:21 GMT

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

Re: Set Analysis - Ignore all selections except certain field

zippo_btl — Wed, 07 Feb 2018 17:13:17 GMT

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

Re: Set Analysis - Ignore all selections except certain field

Anonymous — Thu, 13 Sep 2018 14:15:33 GMT

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)

Re: Set Analysis - Ignore all selections except certain field

Frank_Hartmann — Thu, 13 Sep 2018 19:04:35 GMT

Maybe like that:

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

Re: Set Analysis - Ignore all selections except certain field

wilsonwebb — Fri, 14 Sep 2018 08:04:00 GMT

Hi Eran,

Could you try this:

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