2 Replies Latest reply on Jun 4, 2012 9:37 AM by Tracy Rodgers

    Grouping Dimensions

    Mike J
    Mike J Jun 3, 2012 11:19 AM

      Hi,

       

      I'm looking to group a dimension using a calculated field. I'm having trouble with the lF statement.

      [Group] Dimension Contains

      Group1

      Group2

      Group3

      Group4

      Group5

      NULLs (Blanks)

       

      I need to make up (3) Classes.

      Class A Contains "Group1"

      Class B Contains "Group2"

      Class C Contains "Group3, Group5 and NULLs"

       

      (Note that Group4 is not part of any Class)

       

      Here is my Calcultated field IF Statement:

      IF [Group] = "Group1" THEN "Class A"

      ELSEIF

      [Group] = "Group2 THEN "Class B"

      ELSEIF

      [Group] ="Group3 AND [Group]="Group5" AND ISNULL([GROUP]) THEN "Class C"

      END

       

      I'm not sure what I'm doing wrong.

       

      Thanks for your help.

      Mike.

      • 182 Views
      • Categories: Actions & Filters, Calculations, Queries & Performance
      • Tags:
        • 1. Re: Grouping Dimensions
          Dimitri.B
          Dimitri.B Jun 3, 2012 7:53 PM (in response to Mike J)

          What are the symptoms of the problem, i.e. works but messes up classes, doesn't work at all, etc.?

          To start with, it looks like the condition

          [Group] ="Group3 AND [Group]="Group5"

          can never be true, as it means that the item belongs to both Group3 and Group5 at the same time. It probably should be

          [Group] ="Group3 OR [Group]="Group5"

           

          Also, Group4 rows will be automatically placed into NULL class, as you didn't specify how to handle them via ELSE.

          • 2. Re: Grouping Dimensions
            Tracy Rodgers
            Tracy Rodgers Employee Jun 4, 2012 9:37 AM (in response to Mike J)

            Hi Mike,

             

            Similar to what Dimitri said, but the calculation could be similar to the following:

             

            IF [Group] = "Group1" THEN "Class A"

            ELSEIF

            [Group] = "Group2 THEN "Class B"

            ELSEIF

            [Group] ="Group3" then "Class C"

            elseif

            [Group]="Group5" then "Class C"

            elseif

            [Group]="Group4" then "No Class"

             

            END

             

            -Tracy