Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I'm having trouble combining 3 nested if statements.
Each one works fine on its own and each has the same output (1, 0).
Could anyone offer advice?
Many thanks in advance.
=If(PIR_TIME=Timestamp(Aggr(NODISTINCT Min(PIR_TIME), SURGEON)), 1, 0),
// FC on time
If((Time(PIR_TIME) >= '6:54:00') AND (Time(PIR_TIME) <= '8:36:00'), 1, 0),
// b/w 7-830
If(Time(PIR_TIME) >= Time(SCHED_START_TIME)+5/1440, 1, 0))
//PIR: Sched + 5
Probably spelling or something. Also, the clauses are being badly combined, you should use ANDs to combine the three.
Here you go.
caused by:
=If( (PIR_TIME = Timestamp(Aggr(NODISTINCT Min(PIR_TIME), SURGEON))) AND
(Time(PIR_TIME) >= '6:54:00') AND (Time(PIR_TIME) <= '8:36:00') AND
(Time(PIR_TIME) <= Time(SCHED_START_TIME)+5/1440), 1, 0)
Peter
Try this, good luck
=If(PIR_TIME=Timestamp(Aggr(NODISTINCT Min(PIR_TIME), SURGEON)), 1, If((Time(PIR_TIME) >= '6:54:00') AND (Time(PIR_TIME) <= '8:36:00'), 1,If(Time(PIR_TIME) >= Time(SCHED_START_TIME)+5/1440, 1,0))))
Try this for nested IFs:
=If(PIR_TIME=Timestamp(Aggr(NODISTINCT Min(PIR_TIME), SURGEON)), 1,
If((Time(PIR_TIME) >= '6:54:00') AND (Time(PIR_TIME) <= '8:36:00'), 1,
If(Time(PIR_TIME) >= Time(SCHED_START_TIME)+5/1440, 1, 0)))
=If(PIR_TIME=Timestamp(Aggr(NODISTINCT Min(PIR_TIME), SURGEON)), 'FC on time',
If((Time(PIR_TIME) >= '6:54:00') AND (Time(PIR_TIME) <= '8:36:00'), 'b/w 7-830',
If(Time(PIR_TIME) >= Time(SCHED_START_TIME)+5/1440, 'Sched + 5'))
)
)
Thank you all for your suggestions.
I made a mistake in one of the formulas that I modified: If(Time(PIR_TIME) <= Time(SCHED_START_TIME)+5/1440, 1, 0)
I added the formula in the "Combine Ifs" column.
So for Dr. S, I only want to flag him if he had first case of day (FC of Day), it started the earliest between 7 and 8:30 and if the Patient in Room (PIR) is less than the scheduled start time (adding a 5 minute grace period).
So I only want to show Dr. S' first case beginning at 07:02 because it met all criteria as earliest case in the day.
All other cases should not be flagged.
Do you have a suggestion (I can see if it's a counting issue that the 07:22 case would show too)?
SURGEON | Sched Start | PIR | FC of Day | B/w 7-830 | PIR <= Sched St + 5 | Combine Ifs |
Dr. S | 07:00 | 07:02 | 1 | 1 | 1 | 1 |
Dr. S | 08:30 | 09:10 | 0 | 0 | 0 | 0 |
Dr. S | 07:30 | 07:22 | 1 | 1 | 1 | 1 |
Dr. S | 08:15 | 08:05 | 0 | 1 | 1 | 1 |
Dr. S | 08:45 | 08:47 | 0 | 0 | 1 | 1 |
Dr. S | 09:30 | 09:27 | 0 | 0 | 1 | 1 |
Dr. S | 10:00 | 10:14 | 0 | 0 | 0 | 0 |
Dr. S | 10:45 | 10:57 | 0 | 0 | 0 | 0 |
Probably spelling or something. Also, the clauses are being badly combined, you should use ANDs to combine the three.
Here you go.
caused by:
=If( (PIR_TIME = Timestamp(Aggr(NODISTINCT Min(PIR_TIME), SURGEON))) AND
(Time(PIR_TIME) >= '6:54:00') AND (Time(PIR_TIME) <= '8:36:00') AND
(Time(PIR_TIME) <= Time(SCHED_START_TIME)+5/1440), 1, 0)
Peter
Thank you so much! have a great night!