If then statements in an expression

[johnnitt]

(1)Feb 10 '14 10:12 AM
Hello,

I am trying to calculate hospital sizes adjustment. Here is how the hospital size adjustment is calculated: "Since the largest hospital in the group had 80 times more patient days than the smallest, the size adjustment multiplier was designed to compress the spread to a factor of 2. The size multiplier is 1.0 for the smallest hospital, 2.0 for the largest hospital, and proportionately in between for all hospitals."

Here is the expression I am entering into design view of Access query, with poor results:

Hos_Size_Adj: DMAX (PD)=2, DMIN (PD)=1

I'm not really sure how to put in the expression all of the values for hospital size proportionally in between MAX value (2.0) and MIN value (1.0)


(2)Feb 10 '14 02:43 PM
I am trying to write an expression with several IF-Then statements and getting several errors:

Size_Factor_Index: IIF ([sumPD] = 0, [Size_Factor_Index]= 1) IIF([sumPD] = 133781, [Size_Factor_Index] =2) IIF ([Size_Factor_Index] = [sumPD]/133781 +1)

What is the syntax error in my expression?

Below is the code in SAS that works:

IF sumPD = 0 then value = 1; else If sumPD = 133781 then value =2; else value = sumPD/133781 +1;
run;

[z{Moderator's comment}]
@Johnnitt: I'm not sure if you double posted the question or re-thought the question after the first post; however, the two threads are related now given the posts. [/z{Moderator's comment}]

[mcupito]

Well, for one, I don't see any False Statements, which are required.

http://office.microsoft.com/en-us/access-help/iif-function-HA001228853.aspx

Also, make sure you clean up your calculations, by order of operations. I'm not sure if [sumPD]/133781 +1 is supposed to be ([sumPD]/133781) + 1 or ([sumPD]/(133781 + 1))

[NeoPa]

IIf(X,Y,Z) is a function with three parameters. None of which is optional. The syntax errors in your code are because you haven't passed three parameters.

@Mark.
Division has a higher operational precedence than addition so will always be processed first unless parentheses are used to change the order. Parentheses are not necessary for showing the order of processing unless that differs from the standard order of precedence as specified by BODMAS.

[mcupito]

@NeoPa - I see. I really did wonder about that. I was just trying to suggest something in case maybe the results were miscalculated.

Your knowledge amazes me, NeoPa!

[NeoPa]

I'd rather see members try, but fail, to give helpful advice, than to keep to themselves. Keep going and you'll pick up more and more with which to help people.

[johnnitt]

Sorry, I'm a little lost here. Are you telling me that the expression I wrote is stating that all of the parameters can be true. In plain English I want the sum of patient days that are zero assign the Size Factor Index value as one (1.0) and for the sum of patient days that are 133781 assign the Size Factor Index value as two (2.0). For all other sum of patient days assign the Size Factor Index value as the sum of patient days/133781 + 1. How would I write this expression. I am a newbie to writing expressions (and code in general) so please use very basic explanation. Thanks, John

[mcupito]

John - we are saying you used the IIf function incorrectly. Yes, in a way, to your question. You only wrote in the Expression, and the Value If True.

In NeoPa's comment, note that the correct format for an IIf statement is

IIf ( X , Y , Z )

X is the validation. This would be - ([sumPD] = 0)
Y is the value you want if the condition is true. This would be - [Size_Factor_Ind ex] = 1
Z is the value you want if the condition is false. There is no Value If False condition

Use your logic to put in your validation and your values and you got it! Give it a shot.

[NeoPa]

@John, Try what Mark has suggested and see if you can get that to work.

If not, please try again to state your logic in plain English. Leave no words out this time though. Please check that it reads clearly before posting it. Hopefully unnecessary at this point, but if we do get to that stage understanding what you're trying to say can be important.

[johnnitt]

Sorry I'm not explaining this in a coherent way.
I have a column of patient days for each hospital. I need to convert the column of patient days into a proportion between 1 and 2, 1 being the lowest number of patient days (which happens to be zero) and 2 assigned to the highest (which happens to be 133,781 patient days). For all other hospitals (between the lowest and highest) patient days I will be converting to a proportion that is between 1 and 2 by taking the hospital’s patient days and dividing them by 133,781 (the highest patient days of all hospitals) and adding 1 to this proportion.
The individual expressions: SF: IIf([sumPD]=0,1) puts a 1 in Size Factor column for the hospital with lowest patient days .
The individual expressions: SF: IIf([sumPD]=1333781,2) puts a 2 in Size Factor column for the hospitals highest patient days .
The individual expressions: SF: IIf([sumPD]<> 0 or133781,([ sumPD]/133781+1) ) has invalid syntax.

I am trying to tie a these statements together to get to get one column of that has a proportion of patient days all between 1 and 2, with 1 being the lowest and 2 being the highest. Any help would be appreciated. In other words, the column of patient days will all be either 1, 2, or a proportion between 1 and 2.

[zmbd]

Correct me if I am wrong:

• So you have a field named: [SumPD]
• You have a field named: [patientdays]
• You need to have a "ratio" of these such that the returned value is a decimal value from 1 to 2 inclusive
• 1 is to be lowest limit
• 2 is to be the highest limit

Or that's how I read it at first glance... hard to tell what you have


Let's take a guess:
Using the name of the field as a clue to the method of determining the field then your Your
[SumPD] = sum(patientdays]) yes?

The summation will not work for the logic you've given for the ranking field (however, I now see another interpretation of what you've written, as the logic will follow the same course, the subtreatment follows the main):

What you are after appears to be the Maximum of the number of patients days in anyone given location within the recordset thus:
Dmax([patientdays]) let's name that [MaxPD]

Now lets return our attention to the formula

Lower bound of 1
Upper bound of 2
=(1 + ([patientdays]/[MaxPD]))

So when [patientdays] = [MaxPD] ::== ([patientdays]/[MaxPD])=1
for any [patientdays]<[MaxPD] you will return the decimal percentage; thus, a range inclusive 1 to 2 such as 1... 1.005... 1.5... 2

Format your field for the proper number of decimals by using the field properties.

Ahh, however, I can read this as:

You need the [SumPD] / dmax([SumPD])
If you are looking at it from that view point then simply replace the [patientdays] in the above with [SumPD] at the start of the main logic as indicated and then following thru to the same end results.


No need for any IIF, Partition, Switch functions.

ʕ•ᴥ•ʔ

[mcupito]

I would add a criteria on the last IIf to ensure that the proportion column is only examining those rows that don't already have a rank (I.E The Min and Max). Your code is too static - and by that I mean:

What if the next time you run the code, the highest patient days = 140,000? Your query will exclude data. Same with the Minimum (though, not sure if that will change, but it's good practice to handle the exception)

That being said, you need to pay attention what we are telling you, because you keep asking the same question in different format.

You don't need an IIf for the Min and Max, because you can use the Min() Max() functions. However, you might be able to use

IIf( [sumPD] = (DMax(sumPD], [PatientTbl]), 2, [B]Your value you want to assign if the [sumPD] is NOT the max.[/B]

This is another way to do it, just a little differently.

DMin([sumPD], [PatientTbl])

would give you the minimum number of [sumPD], thus be = Size Factor 1.
Use DMax() for the Size Factor 2.

If you did it this way, I would use an update query, and put the values of 1 and 2 into the SizeFactor field.

For the final query, you need to select all of the remaining records that don't have 1 in the SizeFactor field. Why? Because those are records where the [sumPD] is the Minimum (Size Factor 1).

By selecting all of those records, you can then do your calculation. Also, for your calculation - that would also need to be dynamic.

Instead of

IIf([sumPD]<> 0 or133781,([ sumPD]/133781+1) )

You would need to use the DMax() value in lieu of

133781

in case the Max ever changes.

After you have all of your 'rankings', you can then run an update query and change all of your Patient Days into Proportions.

Also note: The Patient Days column does not need to be deleted from my perspective, you can simply create a new field and call it "SizeFactor "

Let me know what you think.