When Will You Need This?

When you need to analyze your raw data and prepare it to represent the arrivals of an Emergency Department with patient acuity levels. This can be used with the Triage model object or to feed into a model created from scratch.

Daily Pattern Arrivals Healthcare

 

Model Object

The following model object will be used to create daily pattern arrivals for healthcare and import data into ProcessModel: Arrivals \ Daily Pattern Arrivals Healthcare.

Ease of Use: Easy

Ease of Modification: Easy

How to Use the Model Object

1. Open Your Model

Open your model or create a new blank model.

2. Insert the Model Object into Your Model

Locate the appropriate location in your model where you would like to place the model object, move the view to the location and then insert the model object.

3. Connect to the Rest of the Model

Connect the route exiting from activity Acuity to the rest of your model.

connect Daily Pattern Arrivals Healthcare to rest model

4. Open the Excel File

Right click on the Daily Pattern Arrivals Healthcare Sheet Excel icon and click on the third option from the bottom to open the Daily Pattern Arrivals Healthcare v-x-x Excel file, where x-x represents the version number of the Excel file.

Opening Daily Pattern Arrivals Healthcare sheet

Click Enable Content if prompted

Enable Macro

5. Enter Raw Data

Once the Daily Pattern Arrivals Healthcare vx-x Excel file opens you should land on the Input worksheet.

1. Copy your date and time data and paste into cell A3. You need at least 6 months of data. Use the Excel date and time format, sample data is included.

2. Copy your acuity / priority levels and paste them into cell B3. Enter the number that is considered the highest acuity level in your system. You must provide a value between 1 to 9, based on what the highest acuity is in your data. Where 1 is the highest acuity.

If you do not have acuity data you can leave the column empty.

3. Enter any holidays into cell C3. The holidays are optional and are only used to help you review data before import.

Sample data must be removed prior to entry of your data.

You must provide at-least 24 weeks worth of data

Have UK dates and time? Use the UK Dates Converter

6. Prepare Arrival Data for ProcessModel

Click the button titled Prepare Arrival Data for ProcessModel and allow 1 minute for the analysis (depends on the speed of your processor and the size of the file). Once completed you will land on the OutputA worksheet.

 Follow instructions to install Histogram add-on before using the sheet

7. Review Your Data

After you have clicked the button titled Prepare Arrival Data for ProcessModel and have landed on the OutputA worksheet, you can start reviewing your data to make sure that anomalies make sense and if not there are removed.

Arrivals by Day of Week & Holidays

The first thing you will find on the OutputA worksheet are the arrivals listed by day of week. The green highlight is a legend for holiday, based on step 5.3 above.

You can review the daily data and see if you can find anomalies, if the anomalies you find do not make sense, you can delete them. For example; See cell C14 in the screenshot below, the value is 101 which is unusual for the day, you may decide to keep the anomaly or remove it based on your scenario. It maybe that the day was a holiday which was not entered or certain day of the year where you expect more arrivals or it’s part of your process, in all these cases, you may decide to keep the data.

Daily Pattern Arrivals Healthcare review daily arrivals

When deleting anomalies, make sure that you do not remove the row but only delete the value from the cell

removing anomoly Daily Pattern Arrivals Healthcare

You can then also review the Arrivals by Day graph by scrolling right. This may reveal interesting facts about your data, like the graph below reveals that Friday usually has the least number of arrivals, while Wednesday usually has the highest.

Daily Pattern Arrivals Healthcare arrivals by day graph

Hourly Pattern for Each Day

Scrolling further right and down should reveal some interesting graphs that show hourly arrivals by day of week. These graphs should also reveal to you some interesting facts about your data, like which hours have the highest and which hours have the least number of arrivals happening. This may help provide a different perspective to how you want to model your scenario.

hourly graphs from Daily Pattern Arrivals Healthcare

Acuity / Priority

If acuity data was entered, scroll further right and to the top to reveal the acuity graph. As was the case with other graphs this should help provide prospective about your raw data and may help with how you model your scenario.

acuity graph for Daily Pattern Arrivals Healthcare

8. Copy Data to ProcessModel

Scroll to the top and back to column A.

1. Copy the data for Monday, in column B from B4 onwards by pressing the Copy Arrivals button. If you have removed any anomalies, don’t worry, the data will be copied correctly and the spaces in columns will be removed automatically.

copy data Daily Pattern Arrivals Healthcare

2. Goto ProcessModel and open Stat::Fit via Tools \ Stat::Fit.

3. Paste the data into Stat::Fit.

Paste in Stat-Fit

4. Fit the data into a distribution by clicking Out-fit in the toolbar and then OK.

auto fit distribution in Daily Pattern Arrivals Healthcare

5. Review the acceptable distributions and then click Export from the toolbar.

export distribution Daily Pattern Arrivals Healthcare

6. Select the acceptable distribution from the Fitted Distribution and then click OK.

select and export distribution Daily Pattern Arrivals Healthcare

7. Return to ProcessModel, click on the arrival route connecting to Monday activity and then paste (CTRL + V) the distribution in the Quantity per arrival field in the Properties Dialog.

Paste arrival Daily Pattern Arrivals Healthcare

Make sure you remove the old distribution before pasting the new one in

8. Return to the Excel file and click Copy Monday on column K.

copy monday Daily Pattern Arrivals Healthcare

9. Return to ProcessModel, click on the Monday activity and then open the Action Logic window via the Properties Dialog. Remove the old action logic and paste (CTRL + V) in the new action logic.

paste action logic in Daily Pattern Arrivals Healthcare

10. Do step 1 to 9 for all days of the week, replace Monday with the appropriate day of the week.

11. If acuity data was entered, scroll to the right to column T and click the Copy Acuity button.

copy acuity button in Daily Pattern Arrivals Healthcare

12. Return to ProcessModel, click on the Acuity Calculator activity and then open the Action Logic window via the Properties Dialog. Remove the old action logic and paste (CTRL + V) in the new action logic.

paste acuity data in Daily Pattern Arrivals Healthcare

9. Completion

The daily patterns have now been added to your model. You can now save and then simulate the model.