AnsweredAssumed Answered

Trouble on complicated Case calculation with multiple conditions and data types (number and date)

Question asked by mgteval on Jan 23, 2019
Latest reply on Jan 24, 2019 by user19752

I am having the hardest time getting a calculation to work and I am thinking I must just be way off base. I have a series of related fields (listed below) that correspond to each other in pairs:

 

TEST1 and TEST1 Date

TEST2 and TEST2 Date

TEST3 and TEST3 Date

TEST4 and TEST4 Date

etc... through 9

 

The "TEST" fields are a series of test scores. The "TEST Date" fields are a series of dates when the corresponding test score was earned (e.g. record 1 earned an 80 on 1/1/1999, an 85 on 1/5/1999, etc.).  I am trying to calculate a field that will select the highest earned test score and, in the event of a "tie" (e.g. 80 on 1/1/1999 and 80 on 2/1/2001), will choose the later date. I have the following formula, sorry if its no good:

 

TESTPrimary=

Let ([

S=Max ( TEST1;TEST2;TEST3;TEST4;TEST5;TEST6;TEST7;TEST8;TEST9);

D=Max (TEST1 Date;TEST2 Date;TEST3 Date;TEST4 Date;TEST5 Date;TEST6 Date;TEST7 Date;TEST8 Date;TEST9 Date; )

];

 

Case (

S=TEST1 and D=TEST1 Date; TEST1;

S=TEST2 and D=TEST2 Date; TEST2;

S=TEST3 and D=TEST3 Date; TEST3;

S=TEST4 and D=TEST4 Date; TEST4;

S=TEST5 and D=TEST5 Date; TEST5;

S=TEST6 and D=TEST6 Date; TEST6;

S=TEST7 and D=TEST7 Date; TEST7;

S=TEST8 and D=TEST8 Date; TEST8;

S=TEST9 and D=TEST9 Date; TEST9;"NA" )

 

)

 

I need this because another field is populating a date based on what is yielded in the above formula.

 

TESTPrimary Date=

Case ( TESTPrimary=TEST1; TEST1 Date;

TESTPrimary=TEST2;TEST2 Date;

TESTPrimary=TEST3;TEST3 Date;

TESTPrimary=TEST4;TEST4 Date;

TESTPrimary=TEST5;TEST5 Date;

TESTPrimary=TEST6;TEST6 Date;

TESTPrimary=TEST7;TEST7 Date;

TESTPrimary=TEST8;TEST8 Date;

TESTPrimary=TEST9;TEST9 Date;

"N/A")

 

I am up for any suggestions here. Thanks for the help!

Outcomes