topic Indirect Set Analysis and Greater Than in QlikView

Indirect Set Analysis and Greater Than

Wed, 01 Feb 2012 17:23:37 GMT

Hello all.

Concept:

I have a document containing various information about clients, sales, etc.

As part of my load, I also have a separate data construct called protocols, which are not directly linked to clients/etc. but contain a set of metric definitions.

In this example, I am needing to pull all Clients from the current selection who have a Age within the bounds specified by ProtocolGroup_ID = 1.

The following expression works:

=count({$<ClientAge=P({1<ProtocolGroup_ID={'1'}>}AgeMin)>} DISTINCT ClientID)

The expression above works to provide a count of all Clients in current selection who have an age matching the AgeMin value designated by the ProtocolGroup_ID. I need it to provide a count of all Clients who have an age Greater Than or Equal to the AgeMin value.

I have been unable to get it working.

Any insight would be most appreciated.

Thank you in advance for your time and consideration.

Indirect Set Analysis and Greater Than

CELAMBARASAN — Wed, 01 Feb 2012 17:39:24 GMT

Hi,

Try with this expression..

=count({$<ProtocolGroup_ID={'1'},ClientAge={"<=$(AgeMin)"}>} DISTINCT ClientID)

Hope this is your need

Celambarasan

Indirect Set Analysis and Greater Than

Wed, 01 Feb 2012 18:00:17 GMT

Hey Celambarasan.

Thanks for the help.

Unfortunately, that won't work because the ProtocolGroup_ID has nothing to do with the current selection.

If I use the expression you provided, I get the full count of Distinct ClientIDs in my selection, not restricted by age.

Indirect Set Analysis and Greater Than

Wed, 01 Feb 2012 19:33:08 GMT

Hi,

Try this! It should work, if I understood it correctly.

count(DISTINCT {<Age={"<=$(=max({<protocol={'g3'}>}agemin))"}>}ClientID)

...

Ashutosh

Indirect Set Analysis and Greater Than

Wed, 01 Feb 2012 19:33:59 GMT

just change field names according to your fields.

Indirect Set Analysis and Greater Than

swuehl — Wed, 01 Feb 2012 23:11:45 GMT

So your AgeMin is an unambiguous single value? Maybe you could use

=count({$<ClientAge = {">=$(=only({1<ProtocolGroup_ID={1}>} AgeMin))"} >} DISTINCT ClientID)

Indirect Set Analysis and Greater Than

Thu, 02 Feb 2012 05:02:25 GMT

oh yes...I did not notice that he wanted greater than or equal to. So what i wrote earlier should be changed to

count(DISTINCT {<Age={">=$(=max({<protocol={'g3'}>}agemin))"}>}ClientID)

or may be only can also be used but I will personally prefer max.

count(DISTINCT {<Age={">=$(=only({<protocol={'g3'}>}agemin))"}>}ClientID)

...

Thanks

Ashutosh

Indirect Set Analysis and Greater Than

Fri, 03 Feb 2012 12:46:07 GMT

Hi,

what i understood this may help you

let say between 1 to 12 :

=count({$<ClientAge=P({1<ProtocolGroup_ID={'>=1<=12'}>}AgeMin)>} DISTINCT ClientID)

====Else you can Create a Variable and pass instead of Numbers ==========================

=count({$<ClientAge=P({1<ProtocolGroup_ID={">=$(vMin) <=$(vMax)"}>}AgeMin)>} DISTINCT ClientID)

Indirect Set Analysis and Greater Than

Fri, 03 Feb 2012 15:04:35 GMT

=count({$<ClientAge = {">=$(=only({1<ProtocolGroup_ID={1}>} AgeMin))"} >} DISTINCT ClientId)

that should be correct, if the AgeMin is not a variable but a with same value of field or single value only

Indirect Set Analysis and Greater Than

Fri, 03 Feb 2012 18:31:43 GMT

Hey Dwi.

Your code is spot on and works great. Thanks!

What would I need to modify so that the count is for ClientAge >= AgeMin and ClientAge <= AgeMax?

Re: Indirect Set Analysis and Greater Than

Fri, 03 Feb 2012 19:16:39 GMT

I was able to get it working.

Thanks!

And thanks to everyone who stopped by to help out.