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

    Creating Sets

    Zeke Lopez

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


      Compliance 45

      Dominance  50

      Influence  82

      Steadiness 100


      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

      Steadiness 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.






      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
          James Baker

          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
          Person1 Steadiness 100
          Person2 Compliance 66
          Person2 Dominance 90
          Person2 Influence 35


          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
            Zeke Lopez

            Here is a sample of the data,


            Name        Nat Behavior Category    Nat Behavior Scores

            Jane Doe        Dominance            72

            Jane Doe        Influence            62

            Jane Doe        Steadiness            34

            Jane Doe        Compliance            33

            Carol Smith  Dominance            58

            Carol Smith  Influence            52

            Carol Smith  Steadiness            69

            Carol Smith  Compliance            24

            Navy Blue        Dominance            64

            Navy Blue        Influence            74

            Navy Blue        Steadiness            25

            Navy Blue        Compliance            33

            Bill Carol    Dominance            23

            Bill Carol    Influence            74

            Bill Carol    Steadiness            34

            Bill Carol    Compliance            75

            Stacy Cute    Dominance            23

            Stacy Cute    Influence            39

            Stacy Cute    Steadiness            93

            Stacy Cute    Compliance            61

            Adam Getty    Dominance            35

            Adam Getty    Influence            52

            Adam Getty    Steadiness            93

            Adam Getty    compliance            51



            • 3. Re: Creating Sets
              Zeke Lopez

              I created this but I created a different problem.


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

              ELSE 0



              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
                Zeke Lopez

                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








                But I want the results set to look like this


                Dominance Influence Steadiness Compliance

                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
                  Joe Mako



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








                  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:




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




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




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




                  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
                    James Baker

                    Joe beat me to it.