7 Replies Latest reply on Dec 19, 2016 12:12 AM by Yuriy Fal

    How to calculate averages on aggregated data?

    Boreak Silk

      Hi All,

       

      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.

       

      Questions

      1. 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.
      2. 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.

       

      Questions

      1. 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. 
      2. 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.

       

      Questions

      1. 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.

       

       

      Boreak