Lots of data, not sure how to present it, or create spreadsheet for it

[Away_Soldier]

I have DATA, and not sure how to represent it. I have a years worth of daily data that shows 3-4 events that can happen at any time of the day. I would like to know how to create a spreadsheet that I can use to create charts that would show the trends of these three to four events.


Preferable this chart would show days of the week along one side, time of day on the other side and then the number of times the varioius events happened on the Sunday through Saturday axis and the time of day on the other axis. As it stands now, I am manually creating a pinwheel (I probably need to explain that as well) in a Powerpoint slide every time these events happen.


This pinwheel is is basically a radiating point that fans out, the outside of the pinwheel represents the hours of the day, and then I have concentric circles going down to the point, and the arms of the wheel represent the day of the week.


A radar chart might work for my needs, but I do not know how to populate my spreadsheet to show the data correctly. So basically what I really am looking for is a way to see how often these events happen daily, and secondly what time of the day they happen most often.


If anyone can help, I would most certainly be greatful!

 

[jeremymjp]

Maybe a bubble scatter with x-axis as "day of week" and y-axis as "time of day"? The bubble size is the number of times it occurred during that day/time combination.


I'm not a big fan of pinwheel, so maybe someone else should weigh in on that one. Maybe a bar chart with the 168 hours of a week on the x-axis and the number of occurrences of the events on the y-axis (with the color of the bar indicating which type of event) might work.

 

[Mike86]

You could have a lot of fun with this. I'd suggest something interactive. Chandoo's series on Dashboards is really helpful and a good starting point.


Maybe do a Radar chart of each day showing the time of day for each event with the different events in different colors. Use an elevator control to scroll between the seven days. You could also add start / stop date ranges.


Scatter charts for events per week over last twelve weeks and events per month over last twelve months would be easy. Make it interactive with radio button controls to show each event separately or all together. With individual events you could then throw on averages and +/- three standard deviation lines for trend evaluation.


I'm not a big fan of doughnut graphs, but they could work in this case. Make each event a different ring with counts for the last month.


With event data you often have people, activities, or areas also recorded. It'd easy to further stratify the data, if something like that exists.

 

[Jack]

Hi I would think scatter with the top 3-4 being presented in bold colours and the rest in subtle colour. Perhaps Wordle can highlight the main 4 events occurring and show a perspective on all the minor occurances. I also really agree with Mike86, if you can do Chandoo's Dashboard course which I just slowly worked my way through (so much material provided) it just opens your eyes to what is possible and that interactive is the way to go if you can and his guidence on design, colours, fonts etc are so important to getting a smooth attactive result. The art of it all is simplification of data that's the aim.

 

[Away_Soldier]

Thank you all for your replies.


Should I populate this spreadsheet with just three columns then? The basic data I would have to fill in the day, time and the event. This data is found in various documents that are passed on to me in emails, there was no standards created when this project was required to be reported for this position I am in.


As for being interactive, I do not have a need for that, as once I capture all of this information into a spreadsheet, it will be briefed in a much larger briefing so I really only have one to two slides at most I need to worry about creating. I just want to be able to show generally when these events happen by day of the week, and secondly what hour of the day.


Thanks again.

 

[Jack]

AS, sounds like this is all you need, maybe you need a 4th for comments or more detail of the occurence that may be helpful later and you could report these in tabular form on a dashboard if you ever set one up, who knows.


I would set the spreadsheet up with a little table out of the road of expanding data defining the events you commonly encounter leaving room for some new ones also and then point to this using Validation List untick Ignore Blank and add in the range of this table. This way you get a simple drop down and your events are consistently entered. I actually always have a seperate sheet for this called Control where I put stuff like this completely seperate to the data. But if you do this you then need to use a range name for the table and in in valudation list enter =rangename of Validation won't like it because it's on a seperate sheet.

 

[Mike86]

AS, Make the Date/Time a single cell. It's how Excel deals with it anyway and you can extract day, month, year, AM/PM, etc., from that.


Any other factor that is commonly also provided in your documentation and might help with this analysis should get recorded. Anything relating to a quantitative value is usually very helpful. (eg This event required 15 man-hours to correct, cost $33.42 to replace) Count / frequency data is more limited in what you can do analysis for. If you're going to be burrowing for data, you may not have a complete data set. However, often even limited data can lead to an insight.

 

[Away_Soldier]

Thanks again everyone for the input.


I believe I understand how I will make my spreadsheet:

First Column (Date/Time) Second Column Event1, 3rd Col Event2, 4th Col Event3


I will place an X in the column for the event when it happens on the datetime column, and I guess I can then do some magic stuff with countif and other stuff and eventually come up with some graph showing what day of the week these events happen and typical time of the day somewhere in the day of the week graph. Looks like I get to have some fun first populating rows with my mounds of data first, then start playing around to find trends for when things happen. I really don't have an additional quantitative values to go with these events, I need to show when they happen, as things continue as normal when these events trigger, just need to know how often or what trend they happen on.


Take care everyone.

 

[Mike86]

AS -


Just two columns could do this:


Date/Time Event Type


Event Type could be a single character or longer text from a drop-down list. Come back when you get some data points. Graphing is really simple!