Help with duplicate entries

[kattlees]

I am trying to get a count of Surgerys on a specific date. I have run into a scenario where someone is schedule for x type of surgery and it should have been a y type of surgery.  So in our system, they have to cancel the x surgery and reenter it as y type.

Category is decided with

Surgery = if Entry Date < Serv Date and Stopcode =OR

Same Day Cancel = if (StopDate=ServDate and StopCode=CA

Add on = if Entry Date=ServDate and StopCode=OR

Cancels = if Entry Date < ServDate and StopCode=CA

Here is some sample data

PatNo          ServDate                   EntryDate                     StopDate                  Task              StopCode            Category

123456         1/4/2018  13:00       12/15/2017 08:15        1/4/2018 14:18        SurgeryX           OR                     Surgery

543210         1/4/2018  09:30       11/15/2017 13:45        1/4/2018 07:00        SurgeryM          CA                     Same Day Cancel

543210         1/4/2018  09:30       1/4/2018  12:25           1/4/2018 11:00        SurgeryQ           OR                    Add On

987654         1/4/2018 11:00        12/20/2017 09:00        1/2/2018 10:15        SurgeryB            CA                    Cancelled

In scenario above, my system is counting PatNo 543210 as both an Add On and a Cancellation for the same day. The reason there are two entries is the task was entered wrong. We have no way of editing the task so it has to be cancelled and re-entered. Is there a way to count this as a Surgery since it was entered prior to the service date?

Final report should look like this

Date                Scheduled            Add Ons              Cancels           Same day Cancels              Completed

1/4/2018            3                            0                         1                             0                                    2

[v-jianhe-msft]

Hi,

Based on the logic

>>> Surgery = if Entry Date < Serv Date and Stopcode =OR

But for the second record

>>> 543210    1/4/2018  09:30     11/15/2017 13:45    1/4/2018 07:00     SurgeryM      CA         Same Day Cancel

Where, Stopcode is equal to “CA”,

for the third record

>>> 543210     1/4/2018  09:30   1/4/2018  12:25    1/4/2018 11:00        SurgeryQ           OR       Add On

Where, Entry Date is not smaller than Serv Date.

>>> Is there a way to count this as a Surgery

Since 543210 does not meet the surgery condition for both two records above, why is it still ought to be counted as Surgery?

Also, according to the expected final report result, you could count category which is not cancelled as Scheduled.

Best Regards,

Henry

[kattlees]

V-jianhe-msft - those are the measures I have set to count cancelled, Surgery, etc.

See here:

Surgery Catetory = if(V_EWS_TASK_RECORD[FinalType]="Cancelled" && V_EWS_TASK_RECORD[esp1_stop_dt]=V_EWS_TASK_RECORD[ServDate],"2SD Cancel",if(V_EWS_TASK_RECORD[FinalType]="Cancelled" && V_EWS_TASK_RECORD[esp1_stop_dt]< V_EWS_TASK_RECORD[ServDate],"1Cancelled Prior",if(V_EWS_TASK_RECORD[FinalType]="Surgery" && V_EWS_TASK_RECORD[evdet_entry_dt]= V_EWS_TASK_RECORD[ServDate],"3Add Ons", if(V_EWS_TASK_RECORD[FinalType]="Cancelled" && V_EWS_TASK_RECORD[esp1_stop_dt]=V_EWS_TASK_RECORD[ServDate],"2SD Cancel","4Completed"))))

Scheduled = CALCULATE(DISTINCTCOUNT(V_EWS_TASK_RECORD[esp1_pat]),FILTER(V_EWS_TASK_RECORD,V_EWS_TASK_RECORD[LocFinal]="OR" && V_EWS_TASK_RECORD[Surgery Catetory]<>"1Cancelled Prior" && V_EWS_TASK_RECORD[Surgery Catetory]<>"3Add Ons"))

Add Ons = COUNTROWS(FILTER(V_EWS_TASK_RECORD,V_EWS_TASK_RECORD[Surgery Catetory]="3Add Ons"))

Cancellations = COUNTROWS(FILTER(V_EWS_TASK_RECORD,V_EWS_TASK_RECORD[Surgery Catetory]="2SD Cancel" && V_EWS_TASK_RECORD[Loc]="OR"))

Completed = [Scheduled]+[Add Ons]-[Cancellations]

The problem is it counts both rows.

Here is a screenshot of data for Jan. 4  I have problem issues marked as 1,2,3,4

In both 1 and 3 - the wrong surgery type was entered and it was caught on day of surgery. So the wrong one was cancelled, then a new one was added in.

My system is counting them as scheduled, cancelled, an add on and completed. If the mistake hadn't been made, it would only be a scheduled and completed.

In both 2 and 4, the same person had two different surgeries at the same time. THis is pretty common. In these cases, I only need them counted once

I am not sure if what I need is possible. If it isn't, then we will have to find a work around a different way. 

[v-jianhe-msft]

Hi,

Thank you for your reply. I think you could add a filter filtering the conditions where two different surgeries at the same time.

BR,

Henry