4 Replies Latest reply on Oct 1, 2019 3:04 PM by Sheetal Chima

    lookup first value in a group

    Sheetal Chima

      I have data that looks like this:

       

      GroupA      GroupB      Usercount   Crossover %  

      abc            abc             100                 100%         

      abc            def               50                   50%            

      abc            ghi               40                    40%

       

      def             def             50                    100%

      def             ghi             20                      40%

       

      The crossover should be calculated like this -- 100/100

      50/100

      40/100

       

      Then it needs to restart when the GroupA changes - so

      50/50

      20/50

       

      I am using the calculated field for crossover% as

      Sum([usercount])/LOOKUP(SUM([usercount]), FIRST())

       

      But this is basically incorrect, because when the group A changes, it is still taking the denominator as the 100 (the very first row) - so 50/100, and 20/100.

       

      How can this be solved?

        • 1. Re: lookup first value in a group
          Jim Dehner

          you need to reset the start point

          see an example belwo

           

           

          Jim

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          • 2. Re: lookup first value in a group
            Sheetal Chima

            I tried this but what it's doing is showing 100% for Group B in the 3 row example, and also computing the prior 2 based on the 100 from GROUP A

             

             

            abc            abc             100                 100%        

            abc            def               50                   50%           

            abc            ghi               40                    40%

             

            def             def             50                    50%

            def             ghi             20                      20%

            def             abc             10                     100%   <----

            • 3. Re: lookup first value in a group
              Deepak Rai

              Hi Sheetal,

              You need this:

              Thanks

              Deepak

              If it Helps, Pl Mark it Helpful and CORRECT to Close Thread

              • 4. Re: lookup first value in a group
                Sheetal Chima

                I had to use the Sort option in this in addition to what you provided to make it work correctly. thanks!