MIS Systems Thread, MAX Formula taking unwanted results from other result sets in Technical; Help! I have 2 aspects in SIMS, one for banked GCSE results where pupils have already taken the qualification and ...
1. ## MAX Formula taking unwanted results from other result sets

Help!

I have 2 aspects in SIMS, one for banked GCSE results where pupils have already taken the qualification and another for their current predicted grade, which is sometimes in the same subject.
The predicted grade is updated on a half termly basis and the results go into a result set for each collection so I have Year11 Data Collection (DC) 1,2,3,4,5&6 for example.

So I have:
BG Maths GCSE (Highest 1-09-2010 to 31-08-2014)
PG Maths (Year11 DC5)

I am trying to pull out the highest grade of either the banked or latest predicted grade, but where some pupils have been predicted a 'U' with a 0 points score attached or an 'N' (which is what our teachers enter when they have been unable to grade a student) which has no value attached to it at all, the MAX function is pulling out the last grade with a value of > 0 from all other results associated with that aspect, the highest of any grade from any other result set.

It works fine when there is a lower result entered in PG Maths (Year11 DC5) than has been previously if it has a value its just those pesky grades which don't have one, or one on 0.

I could live with it just being a problem with the N grades but it is a big problem for students predicted a U!

Anyone have any way around this as it is really messing up my calculations!

2. OK, let me make sure I understand;

- you have two columns using two different aspects in a template
- the first column is based on a date range, displaying the best result over that date range
- the second column is linked to a resultset, so shows just the result for that resultset
- when you perform a 'MAX' formula against the two columns, it is choosing the best non-zero grade from the second column, rather than just looking at the resultset slected?

If so, I'm unable to recreate your problem my end - which version of SIMS are you using?

The only thing that comes to mind is you mention grades with no value - have you tried assigning '0' to them and does that solve the problem?

As far as I'm concerned, what you describe absolutely should not be happening; the 'MAX' formula should not even be aware of any results outside of those you have filtered via data/resultset.

Sorry that's not much help, but it may be that you've discovered a bug rather than there being anything wrong with the way you're doing it!

3. - you have two columns using two different aspects in a template YES
- the first column is based on a date range, displaying the best result over that date range YES
- the second column is linked to a resultset, so shows just the result for that resultset YES
- when you perform a 'MAX' formula against the two columns, it is choosing the best non-zero grade from the second column, rather than just looking at the resultset slected? Only if the resultset actually showing on the template has a U or a N grade (something with no value or a value of 0). If there is a grade with a value of greater than 0 entered it will work correctly.

If so, I'm unable to recreate your problem my end - which version of SIMS are you using? 7.156

The only thing that comes to mind is you mention grades with no value - have you tried assigning '0' to them and does that solve the problem? U does have a value of 0 in my gradeset and it is a problem for U as well as grades with no value.

If I have discovered a bug I can speak to CAPITA I just wanted to make sure I was not being stupid first!

4. It certainly sounds like a bug to me, that's not how MAX is supposed to work (at least I don't think so!) however it's working fine on my machine (also 7.156) so it may not be as simple as 'MAX' being broken...

One thing you could try to resolve the issue in the meantime is to add 'VALUE' formula columns for each aspect, then run your 'MAX' on those 'VALUE' columns - this should take all previous results out of the equation completely so your 'MAX' formula works as expected again.

5. ## Thanks to LosOjos from:

Xeba37 (10th June 2014)

6. Originally Posted by LosOjos
It certainly sounds like a bug to me, that's not how MAX is supposed to work (at least I don't think so!) however it's working fine on my machine (also 7.156) so it may not be as simple as 'MAX' being broken...

One thing you could try to resolve the issue in the meantime is to add 'VALUE' formula columns for each aspect, then run your 'MAX' on those 'VALUE' columns - this should take all previous results out of the equation completely so your 'MAX' formula works as expected again.
Hmm yeah, that would work, but would take time I don't have at the moment (quite a large template).
At least I have a work round if I need it when I have a few spare minutes. Thanks so much

7. Create a formual(s) that adds 10 points to every grade (the two apsects) and then run the max function from that.

SHARE: