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

    Create groups with overlapping members


      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"



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



        • 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



          [Sales of Cold Drinks] :=

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

          THEN [Sales]

          ELSE NULL



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


          I hope this helps,


          • 2. Re: Create groups with overlapping members

            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:



            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):



            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!!