5 Replies Latest reply: Feb 19, 2018 12:13 PM by Josefina Fasoli RSS

    Evaluating Multiple if statements to create a total score

    Rick Hayes
    Rick Hayes Feb 14, 2018 2:35 PM

      I’ve been trying to figure out a solution for the following problem and unfortunately have not been successful.  I hope someone in the community can help – and thank you.

       

      Let’s say we’re having a sales contest and there are 5 different bonuses. Each bonus has a different calculation. I need to go through a series of calculations (or rules / if statements) to determine if each bonus has been met. I then want the total number of bonus met to appear in a table.  We will then provide a dollar figure for each bonus. 


      Here’s an example

      If(a+b >=40, 1, 0)
      If(c/d >=.70, 1, 0)
      If(F-E<10, 1, 0)… and so on.


      I then want to add up the 1’s to come up with a total number of bonuses hit. If only 2 of the statements evaluate as true, then a 2 will appear in the table, if 4 are true, then 4 will appear for that salesperson.  We will then provide a monetary bonus for meeting 1,2,3,4 or all 5 bonuses.


      By using if statements, I cannot make it through each statement to get a total. I’ve thought about Switch / Case, Match, etc. but simply cannot figure this one out.

       

      Does anyone have an idea for a solution?

      Thank you,

      Rick

        • Re: Evaluating Multiple if statements to create a total score
          Josefina Fasoli
          Josefina Fasoli Feb 15, 2018 12:36 PM (in response to Rick Hayes)

          I'd to it this way:

           

          Bonus_tmp;

          Load *,

           

                    If(a+b >=40, 1, 0) as Bonus1

                    If(c/d >=.70, 1, 0) as Bonus2

                    If(F-E<10, 1, 0)...

                                            ... as BonusN

          From Source;

           

          NoConcatenate

           

          Bonus:

          Load *,

                  Bonus1 + Bonus2 + ... + BonusN as TotalBonus

          Resident Bonus_tmp;


          Drop table Bonus_tmp;

           

          Hope it helps!

          Josefina

            • Re: Evaluating Multiple if statements to create a total score
              Rick Hayes
              Rick Hayes Feb 15, 2018 2:36 PM (in response to Josefina Fasoli)

              Hi Josefina -

               

              Thank you for your answer.  I think it's on the right track.  However, I have used set analysis in my measures to calculate the bonuses.  I'm having a difficult time creating the same "filters" in a load statement.  I tried to simplify my question in hopes that it would help in getting a solution.  I have 5 different calculations using set analysis.  One of those calculations is below:

               

              if(avg({ $ <[Pre-Diabetes]={1},  [Grant Patient]={'Grant Patient'}, [HbA1C Current] > }[HbA1C Current])-

              avg({ $ <[Pre-Diabetes]={1},  [Grant Patient]={'Grant Patient'}, [HbA1C Pre-Test] > }[HbA1C Pre-Test])<= -0.4,1,0).

               

              In reality, this calculates if a patient who has pre-diabetes has seen an improvement in the HbA1c labs measures.  If the improvement (meaning going lower) is less than -.4 points, then its considered a win.  I need to do this as an average for different doctors and their patients.

               

              So, if the latest averages of the "current test" = 5.7 and the averages of the Pre-test = 6.2, then the result of the averages is -.5 (5.7-6.2).  This would be a 1 and would count towards a bonus (Bonus1).

               

              There are 4 additional formulas such as the one above.

               

              Do you still think this will work on a Load of a temporary table?

               

              Thank you,

               

              Rick

            • Re: Evaluating Multiple if statements to create a total score
              Josefina Fasoli
              Josefina Fasoli Feb 15, 2018 3:37 PM (in response to Rick Hayes)

              maybe have a temporary table with the columns

               

              Patient | Doctor | avg[HbA1C Current] | avg[HbA1C Pre-Test]



              The main problem here can be if a patient can have different doctors for the same indicators, because in that case the table won't be useful.


              In this case i'd go for the set analysis.

              Have you tried just adding the if's? Like if(clause1, 1, 0) + ... + if(clauseN, 1, 0)