What graphs/charts/statistics can I get with this dataset?

[mariomp]

Experts,
I created event log file to track the door openings.
Over time, my hope is to see how frequently doors are being opened.

I'm not sure exactly what kind of plotting is available. Can I get hourly numbers per door? Perhaps something that averages number of openings per hour based on day of the week?
What data can be gathered from day/time/eventID?
Any simple, pretty charts?

This is just a test file; eventually, I would like to take your ideas and create reports for thermostat (data not included here) run times; perhaps do the same for lights, users coming/going, etc.

Any help with ideas and formulas would be greatly appreciated.



07/05/2017 18:12:41 - Main Hall Back Door Open
07/05/2017 18:12:47 - Nook/Courtyard Door Open
07/05/2017 18:14:59 - Nook/Courtyard Door Open
07/05/2017 18:17:11 - Nook/Courtyard Door Open
07/05/2017 18:17:42 - Nook/Courtyard Door Open
07/05/2017 18:23:36 - Nook/Courtyard Door Open
07/05/2017 18:23:52 - Nook/Courtyard Door Open
07/05/2017 18:27:28 - Main Hall Back Door Open
07/05/2017 18:32:10 - Front Door Open
07/05/2017 18:33:18 - Front Door Open
07/05/2017 18:35:53 - Front Door Open
07/05/2017 18:36:00 - Front Door Open
07/05/2017 18:36:37 - Front Door Open
07/05/2017 18:36:45 - Front Door Open
07/05/2017 18:37:08 - Front Door Open
07/05/2017 18:37:28 - Front Door Open
07/05/2017 18:37:55 - Nook/Courtyard Door Open
07/05/2017 18:38:16 - Front Door Open
07/05/2017 18:38:26 - Nook/Courtyard Door Open
07/05/2017 18:38:30 - Front Door Open
07/05/2017 18:39:13 - Front Door Open
07/05/2017 18:42:03 - Nook/Courtyard Door Open
07/05/2017 18:42:15 - Nook/Courtyard Door Open
07/05/2017 18:46:10 - Nook/Courtyard Door Open
07/05/2017 18:46:17 - Nook/Courtyard Door Open
07/05/2017 18:50:10 - Main Hall Back Door Open
07/05/2017 18:52:50 - Front Door Open
07/05/2017 19:00:56 - Main Hall Back Door Open
07/05/2017 19:02:27 - Nook/Courtyard Door Open
07/05/2017 19:36:02 - Nook/Courtyard Door Open
07/05/2017 19:36:15 - Nook/Courtyard Door Open
07/05/2017 19:51:40 - Main Hall Back Door Open
07/05/2017 19:52:49 - Nook/Courtyard Door Open
07/05/2017 20:13:13 - Nook/Courtyard Door Open
07/05/2017 20:13:29 - Nook/Courtyard Door Open
07/05/2017 20:19:18 - Nook/Courtyard Door Open
07/05/2017 20:19:44 - Nook/Courtyard Door Open
07/05/2017 20:20:38 - Nook/Courtyard Door Open
07/05/2017 20:55:37 - Nook/Courtyard Door Open
07/05/2017 20:59:12 - Nook/Courtyard Door Open
07/05/2017 20:59:21 - Nook/Courtyard Door Open
07/05/2017 21:23:30 - Front Door Open
07/05/2017 21:53:12 - Main Hall Back Door Open
07/05/2017 22:24:05 - Nook/Courtyard Door Open
07/05/2017 22:24:29 - Nook/Courtyard Door Open

[CAntosh]

It's hard to be specific without seeing the data in an actual workbook, but all of the possibilities you mentioned should be doable. My approach would be to have one sheet with your raw data, then use other sheets to pull the statistics you want, which could then be used for charts. COUNTIFS should be able to break down your data by day, hour, door, or various combinations of the three.

If you get stuck, you can post a sample workbook using Go Advanced --> Manage attachments to open the upload window.

[dflak]

Look into using pivot tables. The data is perfectly set up for them (maybe). If these are not three separate cells, then you'll have to parse the data into three separate cells using formulas. I suggest using Excel tables. If you need help, attach a sample workbook and we can walk you through the process.



Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[mariomp]

Thanks for the suggestion.
I thought my browser was broken because clicking on attach (above) showed just a white sliver with seemingly no way to select file.

I'm attaching txt file as that's how the event is spitting data out.
I would love to learn how I can dynamically open excel file and have the most current data displayed, but I figure that's more a macro thing and I can learn how to do that later.

For now, I really would like to know how many times door is opened each hour, maybe peak times, and any other data that can be gathered.

[mariomp]

To further clarify the intent of the report.
Right now there are complains about the temperature comfort in immediate area, bugs entering the building and hardware loosening up.
My intent is to show that all of those issues are due to heavy traffic thru those doors.
Furthermore, once I see how this is done, I can then take that same concept and expend it to other aspects of automated system and track alarm arming, thermostat run times, etc.

[dflak]

FYI: There are ways to import text files or use them as data sources without VBA. We can discuss those after I take a look at what you have. We'll live with copy / paste for the moment

[dflak]

I will make use of Excel Tables here. The main advantages of Excel tables for this application are:
- Excel tables know how big they are so all formulas, charts and pivot tables made from them always reference the exact amount of data they need no matter how much data there is or isn’t.
- Excel tables “remember” and copy down formulas.
- You can reference table columns and cells in terms of the column header instead of cell references. This makes it easier to manage formulas.

Here is more information on tables: http://www.utteraccess.com/wiki/Tables_in_Excel

Attached is the finished product. I suggest you start with a blank book and try to emulate what I did.

So here is what I did. I went to a blank sheet and typed the following in the first row, cells A1:D1 – Event, Date, Time, Item.

I selected one of these cells and on the home ribbon selected Format as Table. I told the application that my table has headers and selected a style. This created the table.

Then I went to the text file and copied and pasted the data into cell A2. Each pasted in row became part of the table.

Unfortunately, the data isn’t quite ready for prime time. We have to break it out into date and time and what happened.

So in cell B2, I added the formula =DATEVALUE(LEFT([@Event],10)) – this formula gets the date part of the string. We need to wrap it in DATEVALUE since we want a real date and not a string that looks like a date.

Since this is a table, and the rest of the cells in the column are blank, the formula copies down automatically.

I had to reformat cell format for the date to look like a date and not a number.

I did similar things to break out the pieces for time and item.

Now that you have the table set up. It is ready for use in the future. Highlight rows 2 to the end, right click and select Delete -> Table Rows and the table is ready to have new data pasted into cell A2. The table will remember the formats and the formulas, so you won’t have to do those again.

Then I clicked in the data and selected Insert -> Pivot table and let it go to a new sheet.

I dragged Date and Time to the column box and item to the rows and event to values. Since events is a string, the pivot table will give you a count of them.

At this point the pivot table doesn’t look that good. So, right click on a time and select Group and then select hours. The pivot table should look a lot better now.

Experiment with dragging things around in the pivot table.

I dragged date from column to filters and the display changed to show a combination of hours rather than hours by day. You can select a specific day if you wish.

I selected a field in the pivot table and then selected Insert Ribbon and selected a column chart.

Right click on the chart and select Select Data and interchange the columns and rows. See what happens to both the pivot table and pivot chart.

About the only thing you may have to do after importing new data is to right click in the pivot table and select the refresh option.

It does seem like the heaviest traffic times of the day are times when it is most likely to be hottest.

Hopefully this is a good enough example that you should be able to do other data as well. Once you get the table set up, it’s all copy / paste from there. You should not have to change a thing unless you want to play with the pivot tables. And yes, you can have more than one pivot table off the same set of data.

[dflak]

P.S. I forgot to tell you: I really didn't type in =DATEVALUE(LEFT([@Event],10) I typed in =DATEVALUE(LEFT( and then selected cell A2 - intellisense filled in the [@Event] part. Intellisense works very well with formulas.

[mariomp]

You are awesome. Thank you.
It'll take me a while to get thru all you've done there -- I appreciate it.