topic Set analysis and Count Function in QlikView

Set analysis and Count Function

summerrain — Thu, 28 Sep 2017 12:01:44 GMT

My data set is this:

ID	Gender
1	Female
2	Male
3	Female
4	Male
5	Female
6	Male
7	Female
8	Male
9	Female
10	Male

I have a listbox and two text box:

Tex box has these expressions:

='Female: '&count( {$<Gender={'Female'}>} ID)

='Male: '&count( {$<Gender={'Male'}>} ID)

I am wondering If anybody knows why set analysis doesn't work here: If I choose Female in the list box it still shows 5 Male in the text box. I know I can achieve correct count with getselectedcount function, but what to do If I want to use set analysis and that it would take into account all the selections? I though $ symbol should do the trick, but id doesn't in this case.

Please also see the app attached.

Re: Set analysis and Count Function

YoussefBelloum — Thu, 28 Sep 2017 12:05:36 GMT

Hi,

what are you trying to do ?

when you choose Female, you want the text box "Male" shows 0 ?

Re: Set analysis and Count Function

sunny_talwar — Thu, 28 Sep 2017 12:07:53 GMT

Try this

='Female: '&count( {$<Gender *= {'Female'}>} ID)

='Male: '&count( {$<Gender *= {'Male'}>} ID)

Re: Set analysis and Count Function

vishsaggi — Thu, 28 Sep 2017 12:13:35 GMT

May be try these

='Female: '&count( {< Gender = {"=GetFieldSelections(Gender) = 'Female' "} >} ID)

='Male: '&count( {< Gender = {"=GetFieldSelections(Gender) = 'Male' "} >} ID)

Re: Set analysis and Count Function

effinty2112 — Thu, 28 Sep 2017 12:13:51 GMT

Hi Vytautas,

The set expression {$<Gender={'Male'}>} tells the aggregation function it is used in to disregard user selections for Gender and instead return the result as if 'Male' had been selected. The whole idea of set analysis is to allow expressions be be calculated over records that are not restricted solely to user selections in the document. For your purposes try Sum(DISTINCT if(Gender = 'Male',ID)).

Regards

Andrew

Re: Set analysis and Count Function

mohammadkhatimi — Thu, 28 Sep 2017 12:23:24 GMT

='Female: '&if(GetFieldSelections(Gender)='Female', count( {$<Gender={'Female'}>} ID),0)

='Male: '&if(GetFieldSelections(Gender)='Male', count( {$<Gender={'Male'}>} ID),0)

Try this..

Regards,

Mohammad

Re: Set analysis and Count Function

YoussefBelloum — Thu, 28 Sep 2017 12:25:45 GMT

Hi Sunny,

first time i see this.. can you explain the meaning of the STAR here ?

Thanks

Re: Set analysis and Count Function

sunny_talwar — Thu, 28 Sep 2017 12:27:10 GMT

Check here

Implicit Set Operators

Re: Set analysis and Count Function

YoussefBelloum — Thu, 28 Sep 2017 12:31:45 GMT

Very nice post, thank you !

Re: Set analysis and Count Function

summerrain — Thu, 28 Sep 2017 13:15:47 GMT

As always, amazing, Thanks!