9 Replies Latest reply: Aug 18, 2017 4:21 AM by Sam Grounds RSS

    Set Analysis - Sum Sales if certain conditions are met

    Abhay Sudhakaran
      NameRoleOpportunity ID
      JohnA Lead001
      JohnA Support001
      RonB Rep002
      RonB Lead002
      TeressaManager003
      JohnManager001
      TeressaC Lead003

       

       

      Opportunity IDOpportunity NameSales
      001Apple100
      002Orange200
      003Pineapple300

       

       

      Requirement: Sum(Sales) if a person has any sort of Lead or Manager roles (avoiding double counting of sale)

      In the above example, John should have a sales amount of 100$ for the opportunity "Apple", because he is a Lead & Manager for that opportunity. But because he has both roles, he should NOT BE DOUBLE COUNTED. same goes for Teressa, her Sales$ should be 300 ONLY.

       

      How do we specify multiple values to match from in a set analysis ?

       

      Something like this: (I know it is syntactically incorrect, but you get the idea what I am looking for)

      Sum({<if(Role = Match(Role, 'A lead', 'B Lead', 'Manager'>} Sales)