Replicating this selection with set analysis

[Anonymous]

I am counting the number of orders in a table, and they get split up in to three categories: Internal, External, and System. Internal and System I was able to figure out the set analysis for with no problem, but with this External I am having problems. When I have two specific list boxes showing I can select from one, then select excluded from the other and it shows me the correct number. So is there any way to achieve such output through set analysis?

In the attachment I have captured what the list boxes look like. So I can better explain what I do to find this number.

First I select the one thats all by itself (ASB_INTERNAL_USERS), that gives me a list in ROLUSER list box, which I then right click and select excluded. Is there anyway to get a representation of these users through set analysis?

I have tried : count({<ROLENAME-={'ASB_INTERNAL_USERS'}>}ORDER_NO)

but this results in a 0 for the count.

I hope I was able to explain this well enough for others to understand. If more info is needed I can provide what is requested.

Thank you

[whiteline · Accepted solution]

Hi.

You can;t  do it that way since there is no other values in ROLENAME.

Depending on your data model you could add something like 'Not assigned' ROLENAME for the desired set of users.

Or you can try something like this:

count({<ROLEUSER={'=count(ROLENAME)=0'}>}ORDER_NO)

[jaimeaguilar]

Hi,

I'm guessing that the values of ROLEUSER that are not related to ASB_INTERNAL_USERS, have a null value in ROLENAME, is that right? if yes, then you can add a value for nulls from script:

in script windows add these 2 lines:

NULLASVALUE ROLENAME;

Set NullValue = 'N/A';  //This is a system variable where you can add any value you want

After reloading your qvw, ROLENAME field will have now 2 values: ASB_INTERNAL_USERS and N/A. This way you can handle null values in set analysis, that is what its seems to be causing trouble

regards

[rwunderlich]

How about:

count({<ROLEUSER=E({<ROLENAME={'ASB_INTERNAL_USERS'}>})>} ORDER_NO)

-Rob

http://masterssummit.com

http://robwunderlich.com

[preminqlik]

hi,

first clear all,

after selecting and excluding add bookmark and for eg say bookmark name "exc_external"

then add it to your exp like below

sum({$*exc_external)Sales)

[Anonymous]

Everyone posted incredibly helpful answers! I was able to achieve exactly what I needed with the first answer, but the following answers didn't quite achieve the number I was looking for. But each one did give me some more insight to what I can do with qlikview to get what I want from it.

Thank you all for you help