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

    Compare Client String data from two dates

    megan.fletcher.1

      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

           

          LOOKUP(sum([Rank]),FIRST())

           

           

          LOOKUP(sum([Rank]),LAST())

           

          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

           

          Thanks,

          Ritesh

          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
            megan.fletcher.1

            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

              Hi,

               

              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

              END

               

              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

              Result:

              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"

              END

               

              Let us know if this help.

               

              Mahfooj

              • 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

                 

                 

                Thanks,

                Ritesh