6 Replies Latest reply on Oct 1, 2009 9:33 AM by James Baker

# Creating Sets

I have one dimension titled Behavior category [Nat Behavioral Category] that includes 4 categories:

Compliance 45

Dominance  50

Influence  82

Each category contains a measure ex., 45, 50,82, 100. I have 33 people who each have their own unique set of 4 scores one for each dimension. What I am trying to do is produce (4) quick filters that looks like this:

Compliance 0 - 100

Dominance  0 - 100

Influence  0 - 100

By using the four quick filters I can narrow down the targeted ranges for each category, winding up with a result set that is the effect of all four categories combined.

Thanks,

Zeke

PS I am a beginner at writing queries can you recommend a book that can help me frame asking the right questions and give me practical examples that I can cut my teeth on?

• ###### 1. Re: Creating Sets

So you're saying that the domain of the Behavior dimension is [Compliance, Dominance, Influence, Steadiness]?  And that there's a measure called something like "Value" which contains a number?

I'm picturing the data in your table looking like this (normalized):

```
Person1 Compliance 45
Person1 Dominance 50
Person1 Influence 82
Person2 Compliance 66
Person2 Dominance 90
Person2 Influence 35
etc...

```

The other denormalized form would look like this:

```
Person1 45 50 82 100
Person2 66 90 35

```

Could you confirm which you've got?  Thanks

• ###### 2. Re: Creating Sets

Here is a sample of the data,

Name        Nat Behavior Category    Nat Behavior Scores

Jane Doe        Dominance            72

Jane Doe        Influence            62

Jane Doe        Compliance            33

Carol Smith  Dominance            58

Carol Smith  Influence            52

Carol Smith  Compliance            24

Navy Blue        Dominance            64

Navy Blue        Influence            74

Navy Blue        Compliance            33

Bill Carol    Dominance            23

Bill Carol    Influence            74

Bill Carol    Compliance            75

Stacy Cute    Dominance            23

Stacy Cute    Influence            39

Stacy Cute    Compliance            61

Thanks

• ###### 3. Re: Creating Sets

I created this but I created a different problem.

IF[Nat Behavior Category] ="Compliance"Then [Nat Behavior Scores]

ELSE 0

END

This query finds all the compliance scores correctly, but it only displays the results set and not the other columns of data. Any thoughts on how to correct this?

Zeke :-)

• ###### 4. Re: Creating Sets

I also tried this with the same result of finding the right values but the result set only displays these values and not the other relevant values in the Nat Behavior Category and related Nat Behavior Scores.

IIF([Nat Behavior Category]="Dominance",[Nat Behavior Scores],NULL)

So the result set looks like this

Dominance

10

59

60

54

But I want the results set to look like this

10          45      65        25

59          100      10        69

60          25      30        12

54          45      100      13

I then want to use an additional filter on the same set with the influence category, then the steadiness and finally compliance so my final result set is the intersection set using the sum of the four filters.

Any ideas?

• ###### 5. Re: Creating Sets

Zeke,

Unless I am missing something, I have no idea how you are getting, from your above sample data:

Dominance

10

59

60

54

Can you help me to understand where those values came from?

My first thought is that they are from another data source.

Going with that, you are on the right track, and will want the following calculated fields to get the filtering ability you are looking for:

Dominance

`IIF([Nat Behavior Category]="Dominance",[Nat Behavior Scores],NULL)`

Influence

`IIF([Nat Behavior Category]="Influence",[Nat Behavior Scores],NULL)`

`IIF([Nat Behavior Category]="Steadiness",[Nat Behavior Scores],NULL)`

Compliance

`IIF([Nat Behavior Category]="Compliance",[Nat Behavior Scores],NULL)`

You will then want to place these on the measure values shelf, and when you add them to the filter shelf, use the SUM aggregation.

You can change the order they are displayed by either editing the sort of "Measure Names", or moving them around in the Measure Values shelf.

I do not know of a way to force the quick filters to always display "0 to 100", it will display the min to max in that field.

Take a look at the attached workbook and see if that is what you are looking for.

• ###### 6. Re: Creating Sets

Joe beat me to it.