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

    Weekly Hour Moving Average

    Fabio Guimaraes

      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.Snap 2016-11-29 at 12.45.27.png

        • 1. Re: Weekly Hour Moving Average
          Walt Reed

          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
            Fabio Guimaraes

            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
              Fabio Guimaraes

              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
                Walt Reed

                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
                  Fabio Guimaraes

                  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
                    Fabio Guimaraes

                    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)