6 Replies Latest reply: Feb 25, 2013 8:38 AM by Matthew Riedl

# Match a dynamic calculated value to a score table

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:
Matt, 100
John, 80
Jimmy, 1500
Matt, 526
John, 162
Jimmy, 842
Matt, 862
John, 420
Jimmy, 4610
];

Issues:
Person, Issues
Matt, 1
John, 0
Jimmy, 52
Matt, 16
John, 7
Jimmy, 24
Matt, 0
John, 19
Jimmy, 105
];

ScoreLookup:
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.

• ###### Re: Match a dynamic calculated value to a score table

Hi

Join the Received and Issues tables and calculate % for each person. To get the score, use the supplied data to create an intervalmatch table with the fields YieldFrom, YieldTo, Score (derived from your inline table) and use interval match to join the score to each name.

I can't do an example right now, but that should start you in the right direction.

Regards

Jonathan

• ###### Re: Match a dynamic calculated value to a score table

Thanks Jonathan. I've been trying to avoid calculating it in the scripts, so the scores will be calculated based on the selections made. For example, Matt could be associated with several different Order Types.

To use interval match, I would probably need to calculate several different scores to allow for data filtering.

• ###### Re: Match a dynamic calculated value to a score table

Matt

Check out the extended intervalmatch function, which allows you associate the interval match with one or more additional fields, to get several scores, like this:

IntervalMatch(Matchfield, key1, key2, key3....)

Hope that helps

Jonathan

• ###### Re: Match a dynamic calculated value to a score table

Hi Matt

Have you got anywhere with this?

I'm interested because I want to do something very similar...

I don't want to use the script because although I can quite easily calculate which tier to allocate to each row of the dimension using current figures, I want the user to be able to view a projection of those figures to the tyear end, and they should be able to alter those figures using a sliding scale of %.

A true what-if dashbaord.

Like you, the only way I can see of doing this at the moment is to put a large nested IF statement in various expressions of charts etc... not ideal and also have to assume what the maximum number of tier options are when really this should be controlled by whatever data is loaded.

Please let me know if you have come across any better options?

Kind regards

Richard

• ###### Re: Match a dynamic calculated value to a score table

I think it would work like:

=max(if(

>= Yield

, Score))

Example attached.

-Rob

http://robwunderlich.com

• ###### Re: Match a dynamic calculated value to a score table

I ended up going the route of nested-if statements. I had to create a few levels of IFs since there were too many for one. Basically, I divided it into tiers sith one IF statement, then had the remaining IF referenced via dollar-sign expansion.

=If( Score > \$(vTier1_MinScore), \$(vTier1_IF)

,If( Score > \$(vTier2_MinScore), \$(vTier2_IF)

...etc.

vTier1_IF would look similar to this..

=If( Score < vTier1_Attainment1, vTier1_Score1

,If( Score < vTier1_Attainment2, vTier1_Score2

,If( Score < vTier1_Attainment3, vTier1_Score3

...etc.

I did not need to hardcode these values into IF statements. I built them in the script from score table provided using a For Loop. This allowed it to be as dynamic as possible with the nested IFs and removed the need to re-code. If the score fact table changed then it would change during the next reload.