I have a calculated metric for each person, which needs to match up against a score table. Unfortunately, this cannot be calculated in the script since it needs to remain dynamic. How can I match the calculated value for each person against the score table? This would be similar to the MATCH function in Excel.
I tried nested IFs, but there are 100 possible values in the score table, which QV seems to error at around 70 nested IFs.
I also tried using Min with set analysis. For example, Min({<Metric={">=$(vFormula)"}>} Score). This only seems to work if I hard-code the value in, but then it does not calculate for each person.
Received:
LOAD * INLINE [
Person, Received
Matt, 100
John, 80
Jimmy, 1500
Matt, 526
John, 162
Jimmy, 842
Matt, 862
John, 420
Jimmy, 4610
];
Issues:
LOAD * INLINE [
Person, Issues
Matt, 1
John, 0
Jimmy, 52
Matt, 16
John, 7
Jimmy, 24
Matt, 0
John, 19
Jimmy, 105
];
ScoreLookup:
LOAD * INLINE [
Yield, Score
99.9997%, 25.00
99.9996%, 24.75
99.9996%, 24.50
99.9996%, 24.25
99.9995%, 24.00
99.9995%, 23.75
99.9994%, 23.50
99.9993%, 23.25
99.9993%, 23.00
99.9992%, 22.75
99.9991%, 22.50
99.9991%, 22.25
99.9990%, 22.00
99.9989%, 21.75
99.9988%, 21.50
99.9987%, 21.25
99.9985%, 21.00
99.9984%, 20.75
99.9983%, 20.50
99.9981%, 20.25
99.9979%, 20.00
99.9977%, 19.75
99.9975%, 19.50
99.9973%, 19.25
99.9971%, 19.00
99.9968%, 18.75
99.9966%, 18.50
99.9963%, 18.25
99.9959%, 18.00
99.9956%, 17.75
99.9952%, 17.50
99.9948%, 17.25
99.9943%, 17.00
99.9938%, 16.75
99.9933%, 16.50
99.9928%, 16.25
99.9922%, 16.00
99.9915%, 15.75
99.9908%, 15.50
99.9900%, 15.25
99.9892%, 15.00
99.9883%, 14.75
99.9874%, 14.50
99.9864%, 14.25
99.9853%, 14.00
99.9841%, 13.75
99.9828%, 13.50
99.9815%, 13.25
99.9800%, 13.00
99.9784%, 12.75
99.9767%, 12.50
99.9730%, 12.25
99.9687%, 12.00
99.9638%, 11.75
99.9581%, 11.50
99.9517%, 11.25
99.9443%, 11.00
99.9359%, 10.75
99.9264%, 10.50
99.9155%, 10.25
99.9032%, 10.00
99.8893%, 9.75
99.8736%, 9.50
99.8559%, 9.25
99.8359%, 9.00
99.8134%, 8.75
99.7882%, 8.50
99.7599%, 8.25
99.7282%, 8.00
99.6928%, 7.75
99.6533%, 7.50
99.6093%, 7.25
99.5604%, 7.00
99.5060%, 6.75
99.4457%, 6.50
99.3790%, 6.25
99.2168%, 6.00
99.0185%, 5.75
98.7776%, 5.50
98.4870%, 5.25
98.1390%, 5.00
97.7250%, 4.75
97.2360%, 4.50
96.6623%, 4.25
95.9941%, 4.00
95.2210%, 3.75
94.3327%, 3.50
93.3193%, 3.25
89.4350%, 3.00
84.1345%, 2.75
77.3373%, 2.50
69.1462%, 2.25
59.8706%, 2.00
50.0000%, 1.75
40.1294%, 1.50
30.8538%, 1.25
22.6627%, 1.00
15.8655%, 0.75
10.5650%, 0.50
0.0000%, 0.00
];
With these two very basic examples loaded, I would then calculate the % for each person - (Sum(Received) - Sum(Issues)) / Sum(Received). This will allow me to filter on any associated fields and have the proper % calculated. This calculated score is then matched against the table. If an exact match cannot be found, it would find the smallest number larger than the calculated %. For example, 99.935% would have a score of 10.75.
I've been searching and cannot find a solution. Thanks for any help.
