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

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.

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

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

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

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

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

Thanks,

Ritesh