topic Re: Col sum comparing dates/time in Discussions

Col sum comparing dates/time

fmcortes1 — Wed, 19 Oct 2016 00:43:24 GMT

Hi, I am trying to identify how to add values if they are within a certain range, i have tried col sum but I do not know how to do compare the table values, (new to scripting )

the idea is that i have customers coming in at a certain time, stay for x minutes and leave, as the get in to the area, occupancy (number of customers) increase, if they are together within a certain time range.

I'd really appreciate guidance, thanks.

Re: Col sum comparing dates/time

mdawson69 — Wed, 19 Oct 2016 00:46:18 GMT

Your inquiry is in line with a test that I have to build for our Model Development project. Your wording is a little confusing, but I surmise that what you effectively need to do is create an occupancy table based on the entry and exit events at a service location for each customer. The occupancy at any given time is complicated by the interdependence of each customer's exit time which may span several entry events. This is a typical simulation modeling problem when attempting to determine utilization, which is a level above what you are attempting.

Before showing the necessary script, I will walk you through the process.

First, I created a data table similar to your example. In this form the data is agent-centric with the entry and exit events recorded in the same row. We need to separate the events by stacking the events.

Now the events have been separated, but by default they are still grouped by the Agent. We need the events in order by time, so the data table needs to be sorted. Before sorting, we also need to make sure that exits occur before entries. The latter may not be important for your purpose, but we always deal with systems with limited capacity, so entry and exit events that have the same timestamp need to be ordered as exit then entry, as the it is most likely that an agent exits a location before a new agent enters. Once that is done we sort the data table by Time and Action.

Now that the events are in the proper order, a column can be added to track occupancy.

The Occupancy column has a formula that determines the occupancy in each row based on the previous occupancy and the action in the current row. The formula also assumes that the first row is the first entry—it should be—and sets the occupancy to 1 for the first row. Now we need to split the table so that we get the original table orientation back with the occupancy reported.

The Occupancy column now shows the occupancy based on each entry, but also accounts for previous exits from the service location. The split operation placed the Occupancy column after the Agent ID column, so the script will move it to the last column.

Here is the script:

Names Default to Here(1);

dt = Current Data Table();

dt << Stack(

Columns(:Entry, :Exit),

Source Label Column("Action"),

Stacked Data Column("Time")

);

Close(dt, NoSave);

dt2 = Current Data Table();

dt2:Action << Set Property("Value Ordering", {"Exit", "Entry"});

dt2 << Sort(

By(:Time, :Action),

Ascending,

Replace Table(1)

);

dt2 << New Column(

"Occupancy",

Formula(

If(Row() == 1,

If(:Action == "Entry",

Lag(:Occupancy, 1) + 1,

Lag(:Occupancy, 1) - 1

)

);

dt2 << Split(

Split By(:Action),

Split(:Time),

Group(:Agent ID)

);

Close(dt2, NoSave);

dt3 = Current Data Table();

dt3 << Move Selected Columns({"Occupancy"}, To Last);

Re: Col sum comparing dates/time

fmcortes1 — Thu, 01 Oct 2015 03:01:34 GMT

This is great! Thanks a lot, I was able to make it work "step by step", for some reason the script did not work as expected, I guess I need to study a little bit more.

Re: Col sum comparing dates/time

saitcopuroglu — Wed, 19 Oct 2016 00:46:57 GMT

Thank you for the input.

I was wondering similar solutions for our occupancy which in this case is a Room Occupancy in Hotel Management.

I have the Check In Date and Check Out Date. When they check in it means they occupied a room and the number of rooms are limited to 516.

How could I calculate row by row the Occupancy in % (number of rooms occupied/516).
how could I calculate the number of rooms occupied for each given day
Furthermore how could I calculate the occupancy by AgencyGroup

Many thanks in advance.

Re: Col sum comparing dates/time

mdawson69 — Thu, 01 Oct 2015 10:31:53 GMT

What were the issues you had running the script? My sample data table has less variables (columns) than yours, so offhand, I know that one issue you would encounter is if you strictly followed my script would be retaining all of the columns through the process. That would require a relatively minor tweak to the code to ensure that all columns are included when new data tables are created in through the process.

Re: Col sum comparing dates/time

mdawson69 — Thu, 01 Oct 2015 12:09:53 GMT

Once you factor in the constraint of a maximum service level—in this case the maximum number of hotel rooms that may be occupied—you are no longer discussing occupancy, as that is simply the count of occupied rooms at some time. Your first bullet point is utilization, which at time t is defined as,

occupancy ÷ total room count

So you would add a column with that formula.

The remaining bullet points fall under “define your requirements,” which is an issue we often have here with some of our clients. I have learned that with inquiries such as this, make sure I know what the client expects before assuming that I know what they mean. For example, the way one of our clients defines wait time—known as delay in simulation modeling—is very different from the way the same metric is defined in most discrete-event simulation texts, let alone what most simulation software reports. Their definition is not wrong—it is very much contextual—but it is not the standard definition of delay.

“How could I calculate the number of rooms occupied for each given day.” Are you asking for a daily running occupancy or a summary of the maximum occupancy for each day?

“Furthermore how could I calculate the occupancy by AgencyGroup” Same as above.

Re: Col sum comparing dates/time

fmcortes1 — Fri, 02 Oct 2015 03:11:52 GMT

I believe it is more an issue with my knowledge with scripting than with the script itself! I will read a little bit more how to implement scripts to try it, I will let you know once it works. Thanks again for your support!

Re: Col sum comparing dates/time

fmcortes1 — Wed, 07 Oct 2015 02:39:08 GMT

I made it work, thank you mdawson69

Re: Col sum comparing dates/time

mdawson69 — Wed, 07 Oct 2015 11:50:05 GMT

Glad to help. As I stated in my original post, your inquiry made me look into something that I will soon need to look into for the project I am currently working on.