-
Re: Evaluating Multiple if statements to create a total score
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 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 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)
-
Re: Evaluating Multiple if statements to create a total score
Rick Hayes Feb 19, 2018 6:40 AM (in response to Josefina Fasoli)Josefina -
Thank you for the hint about how to insert several, but separate if statements. I accomplished my objective by simply putting in one if statement with a 1 for true and 0 for not true. Then I inserted a + sign and then put in another separate if statement and so on. Works like a charm. Thank you for the hints and your time.
-
Re: Evaluating Multiple if statements to create a total score
Josefina Fasoli Feb 19, 2018 12:13 PM (in response to Rick Hayes)You are welcome
-
-