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

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

        • Re: Match a dynamic calculated value to a score table
          Jonathan Dienst

          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
            Richard Simpson

            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
                Rob Wunderlich

                I think it would work like:

                 

                =max(if(

                          aggr(NODISTINCT (Sum(Received) - Sum(Issues)) / Sum(Received),Person)

                           >= 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.