4 Replies Latest reply on Jul 18, 2016 11:23 AM by nigora.sharipova

    Prior 4Wk Avg by Hour

    nigora.sharipova

      Hi,

       

      I am using the following formula to calculate prior 4 week average by hour.

       

      (

      lookup(sum([order]),-24*7)+

      lookup(sum([order]),-24*14)+

      lookup(sum([order]),-24*21)+

      lookup(sum([order]),-24*28)

      ) /4

       

      However, when i filter on a specific date, my 4 week average calculation does not work anymore and i only see actuals for the specified date.I tried to apply the following as a filter (lookup(min(([Date])),0)); however, it did not help.

       

      Would greatly appreciate your suggestions.

       

      Thanks!

        • 1. Re: Prior 4Wk Avg by Hour
          Naveen Agarwal

          Can you post a sample worksheet?

          • 2. Re: Prior 4Wk Avg by Hour
            nigora.sharipova

            Hi Naveen,

             

            Thank you for your response. I attached a sample workbook to my original post.

             

            Tab 1: Original Date Filter - the 4 week average calculation is removed when the worksheet is filtered on a specific date

            Tab 2: Date Filter based on (lookup(min(([Date])),0)) calculation. The filter works; however, i am not able to apply the filter to all worksheets.

             

             

            Thank you again for your help!

            • 3. Re: Prior 4Wk Avg by Hour
              Naveen Agarwal

              It looks like you want to calculate a 4-week moving average for each data point on an hourly basis. One way of doing this would be to use a Table Calculation like this:

               

               

              Make sure you select Hour for the date axis so that the Table calculation is performed over the last 672 hours for each data point. Not that you will not have any value for the moving average for the first 672 data points. If you choose to include the current value in the average, then you can change 672 to 671 in the dialog box.

               

              This should work even when you select a filter for Product and Brd. I was able to generate the following viz which shows the Sum(Cnt) by Product on the left axis and the moving 4-week average on the right axis. Let me know if this is what you are looking for.

               

               

              I am attaching the revised workbook.

              • 4. Re: Prior 4Wk Avg by Hour
                nigora.sharipova

                Hi Naveen,

                 

                Thank you for your response.  The standard calculation in Tableau provides a moving 4wk avg, where I am looking specifically at the prior 4 wk avg by hour. For example, for the 7/11 9AM , I would like to calculate the average of:

                 

                7/4   9AM

                6/27 9AM

                6/20 9AM

                6/13 9AM

                 

                I am able to get it with the formula i provided above; however, when i filter on the date my 4wk avg calculation gets removed.