3 Replies Latest reply: Sep 26, 2017 9:22 PM by Jayant Tibhe RSS

    Set Analysis - To avoid double counting

    Abhay Sudhakaran

      Here is a sample table (Attached)

       

        

      Op_IDOp_NameOp_LeadTeam_MemberTeam_RoleEmp_IDSales
      1ABCJohnAlanLeadA10010
      1ABCJohnAbhaySupportA20010
      1ABCJohnSamSupportA30010
      1ABCJohnJohnSupportA50010
      1ABCJohnJohnLeadA50010
      2DEFRonKyleSupportA40020
      2DEFRonRussellSupportA60020

       

       

      Each opportunity has a Op_Lead and multiple team members.

      What I want:

      To Sum Sales of Opportunity Records if a person is Op_Lead OR is listed as a Lead under "Team_Role"


      For example in Op_ID 1 we should get $10 Sales amount for John, because he is list as a lead under Team_Role and his name is also listed as Op_Lead.

      Similarly for Op_ID 2, we should get $20 for Ron as he is listed as a Op_Lead,  even though he is not listed under Team_Roles


      Wondering how to achieve this ? Any help is appreciated. Thanks

        • Re: Set Analysis - To avoid double counting
          Neil Hepburn

          I suggest you remodel your data into three (3) tables: Opportunities; Staff; Staff Opportunity Role.  Using your data, the tables would be modeled as:

           

          Opportunities:

          Op_ID,Op_Name,Sales

          1,ABC,10

          2,DEF,20

           

          Staff:

          Emp_ID, Staff Name

          A100,Alan

          A200,Abhay

          A300,Sam

          A400,Kyle

          A500,John

          A600,Russell

          AXXX,Ron

           

          [Staff Opportunity Role]:

          Emp_ID,Op_ID,Role

          1,A100,Lead

          1,A100,Lead

          1,A200,Support

          1,A300,Support

          1,A500,Support

          1,A500,Lead

          2,A400,Support

          2,A600,Support

          2,AXX,Lead

           

          Please note: Due to the way the data would be loaded, John is duplicated in the bridge table as Lead.  However, this will not make any difference to totals assuming you are only using the [Staff Opportunity Role] table as strictly a bridge table. But if this is an issue, the [Staff Opportunity Role] table can be easily de-duplicated during load time.

          • Re: Set Analysis - To avoid double counting
            Vishnu Chakravaram

            Try this:

             

            Sum (Total <Op_Lead,Op_ID> distinct Sales)    // Considering Op_Lead,Op_ID are chart dimensions

             

            or

             

            Sum

            (

             

            Aggr (

             

            Sum(distinct Sales)

            ,


            Op_Lead , Op_ID )

             

            )

            • Re: Set Analysis - To avoid double counting
              Jayant Tibhe

              Your table satisfy all the conditions you explaining.... need more data which clearly differentiate between positive and negative scenarios to construct the Set Analysis