junito2001 Posted December 2, 2013 Posted December 2, 2013 Hello, I work for a school and I'm creating an evaluation database for our principals to use. Within that we have proficiency areas in which teachers get evaluated and receive a score of HE, E, NI, or U. I have one domain (that holds the proficiency scores) which a can receive 4 possible combinations (PAR1, PAR2, PAR3, PAR4) of scores (ex.. he, he, he, he or he, ni, e, e, etc....); based on the combination, the teacher will then receive a final score for the entire domain of HE, E, NI or U. So example: Teacher1 gets: PAR1 = HE PAR2 = E PAR3 = E PAR4 = E The teacher's domain final score will be an E. What is the easiest way I can script these domainX scores without writing out each possible combination out in If statements (which is what I started to do but my eyes are beginning to hurt). Here are the criteria: HE if All four PARs are HE Three PARS are HE and one E E if Two PARs are E and two are HE All four PARs are E Three PARs are E and one is either NI or HE NI if Two PARs are E and two are NI Any three PARs are NI One PAR is NI, one PAR is U and two PARS are E or HE U if Two or more PARs are U Any ideas anyone? Please help!!
comment Posted December 2, 2013 Posted December 2, 2013 Nested ifs are for the birds; use the Case() function instead. Note that the order of tests is important. FilterValues() and ValueCount() should also be fairly useful here. 1
doughemi Posted December 2, 2013 Posted December 2, 2013 Create an unstored (per Wim's comment below) calculation field FinalScore. The calculation should be Let([ ~PARlist = List(PAR1;PAR2;PAR3;PAR4); ~list = ¶ & ~PARlist; ~HEtot= PatternCount(~list; "¶HE"); ~Etot= PatternCount(~list; "¶E"); ~NItot= PatternCount(~list; "¶NI"); ~Utot= PatternCount(~list; "¶U") ]; Case( ValueCount(~PARlist) < 4; ""; ~Utot >1; "U"; ~HEtot = 4; "HE"; ~HEtot = 3 and ~Etot = 1; "HE"; ~Etot = 4; "E"; ~Etot = 3 and ~HEtot = 1; "E"; ~Etot = 3 and ~NItot = 1; "E"; ~Etot = 2 and ~HEtot = 2; "E"; "NI" ) )
eos Posted December 2, 2013 Posted December 2, 2013 As comment said: don't use nested If()s, use Case() instead. Actually, you can always use Case(), though it seems that If() has a more natural ring to it. Using ValueCount instead of PatternCount … Let ( [ theList = List ( "HE" ; "E" ; "E" ; "E" ) ; // sample data ; replace with List ( PAR1 ; PAR2 ; PAR3 ; PAR4 ) he = ValueCount ( FilterValues ( theList ; “HE” ) ) ; e = ValueCount ( FilterValues ( theList ; “E” ) ) ; ni = ValueCount ( FilterValues ( theList ; “NI” ) ) ; u = ValueCount ( FilterValues ( theList ; “U” ) ) ] ; Case ( he + e + ni + u < 4 ; "Incomplete or wrong data!" ; he = 4 or ( he = 3 and e = 1 ) ; "HE" ; e = 4 or ( he = 2 and e = 2 ) or ( e = 3 and ( he = 1 or ni = 1 ) ) ; "E" ; ( ni = 1 and u = 1 and ( e = 2 or he = 2 ) ) or ni = 3 or ( e = 2 and ni = 2 ) ; “NI” ; u ≥ 2 ; "U" ) ) btw, what about "HE", "HE", "HE", "U"? Not possible?
Wim Decorte Posted December 2, 2013 Posted December 2, 2013 Create an unstored calculation field FinalScore. I would probably not go with an unstored calc. Seems to me that once a score is calculated it should be stored, not recalculated on the fly every time it is displayed. 1
junito2001 Posted December 3, 2013 Author Posted December 3, 2013 THANK YOU SO MUCH! Worked like a charm!
junito2001 Posted December 10, 2013 Author Posted December 10, 2013 As comment said: don't use nested If()s, use Case() instead. Actually, you can always use Case(), though it seems that If() has a more natural ring to it. Using ValueCount instead of PatternCount … Let ( [ theList = List ( "HE" ; "E" ; "E" ; "E" ) ; // sample data ; replace with List ( PAR1 ; PAR2 ; PAR3 ; PAR4 ) he = ValueCount ( FilterValues ( theList ; “HE” ) ) ; e = ValueCount ( FilterValues ( theList ; “E” ) ) ; ni = ValueCount ( FilterValues ( theList ; “NI” ) ) ; u = ValueCount ( FilterValues ( theList ; “U” ) ) ] ; Case ( he + e + ni + u < 4 ; "Incomplete or wrong data!" ; he = 4 or ( he = 3 and e = 1 ) ; "HE" ; e = 4 or ( he = 2 and e = 2 ) or ( e = 3 and ( he = 1 or ni = 1 ) ) ; "E" ; ( ni = 1 and u = 1 and ( e = 2 or he = 2 ) ) or ni = 3 or ( e = 2 and ni = 2 ) ; “NI” ; u ≥ 2 ; "U" ) ) btw, what about "HE", "HE", "HE", "U"? Not possible? I brought this up to my administration and they mentioned that anything that does not apply to rules above, will receive an NI. Could that be possible using a Case()? I know Case some what like IF statements but how do I get else (everything else) NI.?
doughemi Posted December 11, 2013 Posted December 11, 2013 See my previous post. The last line in the Case statement does not have a condition. If each of the previous conditions fail, the Case returns the final statement, which in this instance is "NI". Eos' calculation is more compact, but I like to list each named set of conditions and then have a default for when all named conditions fail. EDIT: If the administrator's statement means that if one or more of the grades is missing, then the final grade is NI, then remove the line ValueCount(~PARlist) < 4; ""; from the calc.
junito2001 Posted December 13, 2013 Author Posted December 13, 2013 That is exactly what I ended up doing thank you again for all your help. Complete success!
Recommended Posts
This topic is 4266 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now