I’m currently working to visualise pedestrian count data that is
collected from a number of sensors across Melbourne, find attached workbook and
excel file. The data is available at hourly intervals and I’m (a learner) interested
to calculate averages across all or some sensors by dates/times (could be year,
month, weekday/weekend, day of week, ….
Example 1. In ‘Before-After (HOUR)’ workbook, I wanted to see the differences in average hourly pedestrian
activity at all or some of the locations between 2009 and 2010. A way to do this
is to analyse average hourly/daily/weekly/monthly counts. I created a
calculated field, called Before-After, to filter to May-Dec for each year.
- How do I generate total counts for all sensors
or some sensors (depending on my selection) for any given days/times when all
of the selected sensors were operational? This means that the aggregated values
should be null for the day/time when one or more sensors were not operational.
- Then, how do I calculate averages on the total
counts over weekday/weekend or any given day of week?
Example 2. In ‘Before-After (DAY)’ workbook, similar to the above example, I wanted to see the differences
in average daily pedestrian activity on weekday/weekend or any given day of
week for 2009 and 2010 for all the sensors or selected sensors.
- The same as questions 1 but at day level. The data
has already been cleaned – if a sensor is operational for less than 24 hours on
any given day, that day’s data is removed.
- Then, how do I calculate averages for
weekday/weekend or any given day of week?
Example 3. In ‘Weekday_End (DAY)’ workbook, I wanted to calculate average hourly count or daily count for any given
month or months for all sensors or selected sensors.
- How do I do that?
Thanks in advance and any assistance will be greatly appreciated.
I normally analyse the data in SPSS and it would be nice to visualise it in Tableau.