2 Replies Latest reply on Jul 19, 2013 6:40 AM by jon.keller.0

    Create groups with overlapping members

    jon.keller.0

      Is there any way to place multiple groups (where some groups might contain members in other groups) on one sheet and get the correct sales values? For example, there may be two product groups (coffee & cold drinks) but a product (such as iced coffee) might be included in both groups. How can this be done to take into account sales for the product in both groups when placed on the same sheet for comparison?

       

      I've attempted making two separate groups, a single group that includes two groups inside the group (which can't be done since one member can't be placed in more than one group), and also tried a calculated field IF-THEN statement.

       

      Here is an example of the code for creating the group as a calculated field:

       

      IF [Product]="French Roast" THEN "Coffee"

      ELSEIF [Product]="Vanilla" THEN "Coffee"

      ELSEIF [Product]="Hazelnut" THEN "Coffee"

      ELSEIF [Product]="Iced Coffee" THEN "Coffee"

      ELSEIF [Product]="Soda" THEN "Cold Drinks"

      ELSEIF [Product]="Sports Drink" THEN "Cold Drinks"

      ELSEIF [Product]="Iced Coffee" THEN "Cold Drinks"

      END

       

      Your help is greatly appreciated! Let me know if you need any clarification.

       

      Thanks!

        • 1. Re: Create groups with overlapping members
          Robert Morton

          Hi Jon,

           

          The approach I typically take in this case is to create two separate calculated fields which surface the sales value only for the members in each group, and NULL otherwise.

           

          [Sales of Coffee] :=

          IF [Product]="French Roast" OR  [Product]="Vanilla" OR [Product]="Hazelnut" OR [Product]="Iced Coffee"

          THEN [Sales]

          ELSE NULL

          END

           

          [Sales of Cold Drinks] :=

          IF [Product]="Soda" OR [Product]="Sports Drink" OR [Product]="Iced Coffee"

          THEN [Sales]

          ELSE NULL

          END

           

          Once you've created those two calculated fields, place them both on your viz to compare sales in each group.

           

          I hope this helps,

          Robert

          • 2. Re: Create groups with overlapping members
            jon.keller.0

            Hi Robert,

             

            Thanks for the response. Unfortunately, this does still not solve my issue. The method you have suggested (I changed a couple things to fit my needs) provides the information split into 4 rows:

             

            Current.PNG

            Using this you can come up with the correct sales number for each one, but I am looking for a more efficient method:

            Sales of Coffee: 192,083 + 18,257 = 210,340

            Sales of Cold Drinks: 16,857 + 18,257 = 35,114

             

            I need it to display something like this (with the correct sales numbers):

             

            Need.PNG

            As you can see the 'Sales of Cold Drinks" value exlcudes the values that overlap which account for the 18,257

             

            Any other thoughts?

             

            Thank you!!