4 Replies Latest reply on Jul 15, 2018 10:53 AM by Ritesh Bisht

    Compare Client String data from two dates


      I have client data and want to compare two assessments, but can't figure out how even though this seems fairly straight forward to me.


      Say I have clients 1-10. They have taken various assessments, sometimes only one, sometimes 5. I want to compare their first assessment to their last assessment.


      I've created a calculated field to pull my first assessment date and last assessment date but am stuck here. I tried to create a calculated field that said "if first assessment is low and last assessment is high then increase" but it's not working since it's both string/integer data. I'd like to have it pull something like the below table.


      Can anyone help me figure out what I'm doing wrong?


      Dummy Data attached.


      Example of what I'd like to produce:

      ClientFirst ScoreLast ScoreResult
      Client 1lowmediumIncrease
      Client 2lowhighIncrease
      Client 3highlowDecrease
      Client 4highmediumDecrease
        • 1. Re: Compare Client String data from two dates
          Ritesh Bisht

          Hi Megan,


          You can try giving a value for Total Score like below so that you can compare


          if [Total Score1]='High' then 1 ELSEIF  [Total Score1]='medium' then 0

          ELSEIF [Total Score1]='low' then -1 END


          Then make use of so that you can compare







          At the end you need to compare first and last for every ID BY USING BELOW


          if [Lookup First] < [Lookup Last] then 'Increase'



          ELSEIF [Lookup First] = [Lookup Last] then 'Same'



          else 'decrease' END


          Please find the attached for detailed analysis.


          Screen Shot 2018-07-15 at 7.10.20 AM.png




          Please mark is as HELPFUL/CORRECT if it really helped you so that it can help others as well

          • 2. Re: Compare Client String data from two dates

            Thanks, Ritesh. I'm not able to download your example because I have a prior version and my IT team has to download the latest version.


            I did try to follow your directions in the body of your reply and I must have done something wrong, because all of the results I'm getting are "same" or "null".


            I'll take a look at your example next week when I can get our IT team to upgrade my version!

            • 3. Re: Compare Client String data from two dates
              Mahfooj Khan



              Find my approach below,


              Create a calculated field to Rank the scores like this


              Score Rank:

              IF UPPER([Total Score1])="MISSING DATA" THEN 0

              ELSEIF UPPER([Total Score1])="LOW" THEN 1

              ELSEIF UPPER([Total Score1])="MEDIUM" THEN 2

              ELSEIF UPPER([Total Score1])="HIGH" THEN 3



              Then create few more calculated fields to get the first and last assessments scores


              First Assessment:

              IIF([Assessment Date]={FIXED [Cl Id]:MIN([Assessment Date])},[Score Rank],0)


              Last Assessment:

              IIF([Assessment Date]={FIXED [Cl Id]:MAX([Assessment Date])},[Score Rank],0)


              Now final calculated field


              IF SUM([Last Assessment])<SUM([First Assessment]) THEN "Decrease"

              ELSEIF SUM([Last Assessment])=SUM([First Assessment]) THEN "Equal"

              ELSEIF SUM([Last Assessment])>SUM([First Assessment]) THEN "Increase"



              Let us know if this help.



              • 4. Re: Compare Client String data from two dates
                Ritesh Bisht

                Sure you can check.


                I am uploading 10.4 version which you should be able to open


                I think you should take note of this




                Screen Shot 2018-07-15 at 1.58.18 PM.png



                Desired output as below (Steps are the part of caption @ worksheet )


                Screen Shot 2018-07-15 at 11.20.52 PM.png