3 Replies Latest reply on Dec 27, 2012 8:35 AM by Philip Chiappini

    Moving Averages with pills on "level of detail" shelf (and with custom grand totals)

    Philip Chiappini

      I have a calculated measure that relies on two separate moving averages. The calculation works just as expected, but in order to get the correct grand total I'm required to add copies of my dimensions to the "level of detail" shelf, and that completely wrecks my moving averages which have to be computed using Pane (Down).

       

      It seems to be an either-or situation where I can either get the proper moving average without a grand total in the "revenue loss" column, or get a grand total, but have it be completely wrong.

       

      Hopefully the attached workbook demonstrates the problem well enough.

        • 1. Re: Moving Averages with pills on "level of detail" shelf (and with custom grand totals)
          Jonathan Drummey

          Hi,

           

          First of all, when using table calculations I recommend using fixed addressing (a set of one or more dimensions in the view) instead of relative (Table/Pane Across/Down). Just about any result you can get with relative addressing, you can get with fixed addressing. In this case, in your initial view addressing on Date_day gives you the same results as Pane (Down).

           

          It seems like what you want is for the Load_Time_MA to be the moving average, but I'm not clear on what you want for that in the Grand Total row, what sort of aggregation do you want there? Is it the average of the average results for each hour? In that case, you're going to need to do an aggregation of the Load_Time_MA to return the correct results, because you're needing one aggregation in the detail rows and a separate aggregation in the grand total row. It might be possible to use the current technique with duplicated dimensions and a modified Compute Using for the aggregated calc, otherwise you'd need to do one of the following:

           

          - set up a test for the grand total row and return different results there

          - use the Custom SQL technique for duplicating your data to generate your own Grand Total row

          - use the two worksheets on a dashboard technique, where you have a separate worksheet for generating the grand totals

           

          Jonathan

          • 2. Re: Moving Averages with pills on "level of detail" shelf (and with custom grand totals)
            Philip Chiappini

            Hi Jonathan,

             

            Thanks for the response.

             

            I've switched the moving averages to fixed addressing as you've recommended. As far as grand totals for the moving averages, I don't really care what they total to because I won't be their totals for anything (a total of a moving average is worthless really).

             

            I use the MA's for the Revenue Loss column. The calculation of lost revenue is contingent of the load time and add-to-cart rates differing from their moving averages. In the initial view, all of the columns are correct and the only issue is that I need the grand total for the Revenue Loss column to be = to the sum of the above cells (a window_sum). I've tried all of the techniques you mention above (I'd love it if the last one would work as the only number I need for the visualization is the Total Revenue Loss).

             

            I'll continue to fiddle with things, I just wish I didn't have to do so much trial and error when working in Tableau.

             

            Thanks.

            • 3. Re: Moving Averages with pills on "level of detail" shelf (and with custom grand totals)
              Philip Chiappini

              Jonathan,

               

              You're tip to use fixed addressing completely fixed my issue! Thanks so much!

               

              Here's what I I was going for: