topic Evaluating Multiple if statements to create a total score in App Development

Evaluating Multiple if statements to create a total score

Anonymous — Wed, 14 Feb 2018 19:35:55 GMT

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

Anonymous — Thu, 15 Feb 2018 17:36:38 GMT

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

Anonymous — Thu, 15 Feb 2018 19:36:01 GMT

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

Anonymous — Thu, 15 Feb 2018 20:37:44 GMT

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

Anonymous — Mon, 19 Feb 2018 11:40:55 GMT

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

Anonymous — Mon, 19 Feb 2018 17:13:06 GMT

You are welcome