topic Re: Set Analysis, Avoiding all selections except one in QlikView

Set Analysis, Avoiding all selections except one

Thu, 15 Oct 2015 17:06:20 GMT

Hi everyone,

I need to calculate the mesoure below:

=count( DISTINCT{<Estado={'Desactivado'}>} [ID Recurso] )/count( DISTINCT [ID Recurso] )

My problem is that I need to ignore ALL the selections except one(for example, Field_1). I have many fields that can be selected and I cant set each one.

Is there anything i can do to avoid all the selections but the Field_1?

Appreciate your help!

Thanks,

Re: Set Analysis, Avoiding all selections except one

sunny_talwar — Thu, 15 Oct 2015 18:03:10 GMT

Try this:

=Count( DISTINCT{1<Estado={'Desactivado'}, Field_1 = p(Field1)>} [ID Recurso] )/count( DISTINCT {1<Field_1 = p(Field1)>} [ID Recurso] )

Re: Set Analysis, Avoiding all selections except one

maxgro — Thu, 15 Oct 2015 18:40:01 GMT

there is a good doc about your question here

Ignore all selections except some specific fields using Set Analysis

Re: Set Analysis, Avoiding all selections except one

Thu, 15 Oct 2015 19:27:32 GMT

Expression given by Sunny T works. but there is one caveat here, if you have a selection on Field_2 which is associated to Field_1 and only 3 (just example) of them are available for the selection, Field_1 =p(Field_1) while have only 3 possible values for Field_1. If you wanted to ignore all selections except Field_1 you will have to ignore all other fields in the possible set of field_1. like below

Field_1 = p({<all fields except Field_1>}Field_1)

to get the all fields except Field_1 :

in the variable definitions window

vField_1Exclusions = Concat(Distinct '['& if(Not WildMatch([$Field],'Field_1'),[$Field]&']',','))

Field_1 = p({<$(vField_1Exclusions)>}Field_1)

Re: Set Analysis, Avoiding all selections except one

Fri, 16 Oct 2015 07:39:18 GMT

Thanks for your help!

I tried it and it worked!

Re: Set Analysis, Avoiding all selections except one

tresB — Fri, 16 Oct 2015 07:57:19 GMT

I would prefer a simpler one (until someone points out a glitch).

=Count( {1<Field_1=$::Field_1>} YourField)

Credit : bwisenoSimenKG‌

And please be careful,

=Count( {1<Field_1=p(Field_1)>} YourField) , might give you wrong result, because the possible values of Field_1 are still getting affected by other selections, which you don't want.

Re: Set Analysis, Avoiding all selections except one

sunny_talwar — Fri, 16 Oct 2015 10:51:36 GMT

tresesco‌ so you are saying that if we have this -> {1<Field_1 = p(Field_1)>} it can change with selection in Field_2 if they are somehow linked together?

I was under the impression that it won't, would you be able to share a sample where it doesn't work.

Thanks,

Sunny

Re: Set Analysis, Avoiding all selections except one

sunny_talwar — Fri, 16 Oct 2015 11:13:22 GMT

Wow you are right, I have using this p thing for a while without knowing this thing. Thanks tresesco‌ for bringing this to my notice. Really appreciate.

Please find attached a sample where other might be able to see an example.

Best,

Sunny

Re: Set Analysis, Avoiding all selections except one

tresB — Tue, 15 Dec 2020 05:50:12 GMT

I thought that this would be better if I could make a document for the same and just did that here:

Ignore-all-selections-except-few-fields-using-Set-Analysis