6 Replies Latest reply on Dec 2, 2016 2:08 AM by Fabio Guimaraes

Weekly Hour Moving Average

Hi guys!

I'm working with daily+hour (DATEADD('hour',[Hour],[Date] )) data using Google Analytics as a source.

I want to compare my hourly sessions with:

1 - The same hour at the same day of the week. For example,I want to compare my sessions at 14PM on a specific Monday with the average sessions of all my Mondays at 14PM.

To calculate the average sessions of all day of week per hour, I'm using this calculation:   { FIXED DATEPART('weekday', [Date]),[Hour]:AVG([Sessions])}

2 - The same hour historically. For example, I want to compare my sessions at 14 PM on a specific Monday with the average sessions of all 14 PM.

To calculate the average sessions of all hour, I'm using this calculation: { FIXED [Hour]:AVG([Sessions])}

3 - The moving average of the same hour of the last 7 days. For example, I want to compare my sessions at 14 PM on a specific Monday with the Moving Average of all 14 PM of the last 7 days.

I DON'T KNOW HOW TO DO THIS.

I'm just asking if my calculations above are right and also how to make the last calculation.

Appreciate any help here.

ps: Sorry, but I can't share the data. • 1. Re: Weekly Hour Moving Average

Hey Fabio,

Not being able to see your data, here's a suggestion--add SUM(Sessions), then do a Table Calculation of Moving Calculation using Avg. over Prev 7 days. Here it is using the Sample - Superstore data: Cheers,

Walt

1 of 1 people found this helpful
• 2. Re: Weekly Hour Moving Average

Hi Walt

Totally right! Just adding a point here...In my case it was necessary to use "custom sort" by Hour. • 3. Re: Weekly Hour Moving Average

HI Walt!
Sorry, I've got a problem here...

Doing this kind of "Quick Table Calculation", when I "edit in shelf", I can see this formula: WINDOW_AVG(SUM([Sessions]), -7, -1). but I can use this "New variable" in another calculation.

The point is that I can't use this variable for others calculations.

Is it correct?

Tks!

• 4. Re: Weekly Hour Moving Average

Hey Fabio,

I think you're correct--although it might depend on the kind of calculation you want to do. Can you give any example?

Walt

1 of 1 people found this helpful
• 5. Re: Weekly Hour Moving Average

Hi  Walt!

The calculation is working so far , thank you!

But I didn't understand the results when there is not data enough for the calculation.

I can provide the excel file with a sample of the dataset. The first 2 calculations are correct (FixedAVGDayofWeekHourSession and FixedAVGHourSession). The third calculation is also correct for the hour "0" and so on, the problem is that I don't understand the calculation when Tableau doesn't find data enough to make the moving average (as you may see on gray area of my column J).

Please, if you could take a look would help. I promise you that is a very simple doubt

Thank you !

• 6. Re: Weekly Hour Moving Average

HI @Walt

I came up with another problem using the same calculations.

The point now is that I want to consider only the past for all my 3 new fields, let me explain you:

I need 3 calculations

1 - The same hour at the same day of the week BUT FOR THE LAST 7 DAYS. For example,I want to compare my sessions at 14PM on a specific Monday with the average sessions of MY LAST 7 Mondays at 14PM.

To calculate the average sessions of all day of week per hour, I'm using this calculation:   { FIXED DATEPART('weekday', [Date]),[Hour]:AVG([Sessions])} BUT NOW I WANT TO CONSIDER THE LAST 7 DAYS OF WEEK.

2 - The same hour FOR THE LAST 7 DAYS. For example, I want to compare my sessions at 14 PM on a specific Monday with the average sessions of THE LAST 7 DAYS AT 14 PM.

To calculate the average sessions of all hour, I'm using this calculation: { FIXED [Hour]:AVG([Sessions])}BUT NOW I WANT TO CONSIDER THE LAST 7 DAYS CONTINUALLY.

3 - The moving average of the same hour of the last 7 days. For example, I want to compare my sessions at 14 PM on a specific Monday with the Moving Average of all 14 PM of the last 7 days.

For this I'm using the calculation discussed before: WINDOW_AVG(SUM([Sessions]), -7, -1)