Calculated Field Help
I have an interesting use case regarding and I'm wondering if this can be accomplished using calculated fields. If so any guidance on what functions to use or how to do it would be useful.
My use case is if I have an array of values (7 numeric fields), I need to compare all the fields and take the top 5 results, from here I need to return the lowest of the 5.
So if I have values 2, 4, 4, 1, 6, 8, 3 I'd like to compare all of them and narrow my set down to 8, 6, 4, 4, 3. From here I need to return 3. We have a way to do this if all the numbers are unique but if there are duplicate values (as in the above example) we are unable to accomplish this.
In C# this I easy, you just create an array and reference an Index location to return the value you want. Is there a clever way to do this with Archer calculations and functions?
- archer admins
- Community Thread
- Forum Thread
- RSA Archer
- RSA Archer Suite
- Tips and Tricks
I don't think that Archer support data type such as "array of values (7 numeric fields)", there for it is not possible.
If you want to work with them as individual fields, you can probably create 7 calculated fields with 50+ IF statement and calculate them in ascending order. Not sure if this is a good idea through - formula will be complex.
As a developer you can "open" a sort algorithm and describe it with IFs and put into archer formula.
Could you rephrase it and say you're taking the top of the bottom three, instead of the bottom of the top five?
Its like Stan said, you would have to give each number (or cell of the array) its own field. Your output would be in an eighth field that holds the calculation. The eighth field is a calculated numeric field that references the 7 other fields. The seven cells/fields could be on or off layout, but again, the eighth field is what you're concerned about. There would be a good number of IF statements, but it should be doable.
We had to do something like this to compare severities, taking the top severity from three ratings. Your development environment might actually be Excel. It was pointed out that Archer calculations mimic Excel formulas, with limitations as to what functions are available of course.
How are these numbers coming into Archer? It might be better to have a script that solves this and uses the API to write this value.
Okay I lied its going to be pretty hefty. You're going to need 10 fields and 3 calculations. Fields 1-7 hold the original values. Field 8 returns the field name with the lowest value. Field 9 returns the field name with the lowest value excluding the field name in Field 8. Field 10 returns the lowest value of the field excluding those fields found in Fields 8 and 9. I'm not sure Archer works like that, but it would be something along those lines. You're going to have to do a sort first regardless, and treat each field like hardware memory. It might be better to do this comparison outside of Archer.