Create a Multi Race category - Set Analysis

[akmughal]

Hi Guys,

I'm trying to create a category "Multi Race" from two fields using set analysis where it sums the clients count based on their Primary and Secondary races. So, if there is a client who has two races, that client should be counted under the "Multi Race" instead of Primary Race.

Here is an example:

Client ID	Primary Race	Secondary Race
1	White	-
4	White	Black
5	Black	American Indian
6	Asian	-
8	Black	-
9	American Indian	-
12	White	-
13	White	-
14	Black	White
9	Total Client

How would I create a set analysis statement to create a below table?

Race Category	Client Count
White	3
Black	1
Asian	1
American Indian	1
Multi Race	3
Total	9

Thank you,

akmughal

[rwunderlich]

This could not be done with set analysis, but may be done with if() using a calculated dimension like:

Aggr(if(len([Secondary Race], 'Multi Race', [Primary Race]), [Client Id])

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com