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

# How to calculate averages on aggregated data?

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

• ###### 1. Re: How to calculate averages on aggregated data?

Hey Boreak Silk,

Such interesting questions!

I'm raising this post, I'm sure our community would be able to help you regarding theses questions.

Calling Data Heroes to help Boreak!

• ###### 2. Re: How to calculate averages on aggregated data?

Hi Boreak,

These questions has blown my mind. Reading them again and again.

-Ashish

• ###### 3. Re: How to calculate averages on aggregated data?

Hi Boreak,

Please find the attached -- as of my (flawed, perhaps) understanding.

What's been done is a combination of an Aggregate Conditional Filter on YMD --

to remove those particular days where not every Sensor ID has Hourly Counts --

and an Aggregate Ratio Calculation -- to get an (weighted) Average Hourly Counts

for the domain-complete days (where all filtered Sensors has data in).

So this would be a kind of L2L comparison (imho).

I've used a table view on Sheet 7 to check the logic.

If you feel it's flawed or has no sense to you --

As for the findings, there is a surge at Midnight (hour = 0)

in Weekdays of 2010 (as compared to 2009) in some places,

though there may be an artifact.

Yours,

Yuri

• ###### 4. Re: How to calculate averages on aggregated data?

Bi Yuri,

Thank you very much for your help.

I tested your calculated fields with two sensors combined (Sensor 1 and 2) between 1 Oct and 30 Nov 2010

and I got slightly different results.

Average pedestrian counts for these two sensors combined for the above period, Sensor 1 was not operational

between 10 Nov and 30 Nov, should be:

00:00 - 170

01:00 - 114 ...

but the calculated fields generated slightly different results:

00:00 - 156

01:00 - 104 ...

I think because the calculated fields add counts of those two locations together for all 61 days even though one of them was not operational

between 10 Nov and 30 Nov 2010.

Regards

Boreak

• ###### 5. Re: How to calculate averages on aggregated data?

Hi Boreak,

between 10 Nov and 30 Nov, should be:

00:00 - 170

01:00 - 114 ...

Actually, they are.

(check results on Sheet 7).

Yours,

Yuri

• ###### 6. Re: How to calculate averages on aggregated data?

Hi Yuri,

Brilliant, thanks.

Regards

Boreak

• ###### 7. Re: How to calculate averages on aggregated data?

Nice.