3 Replies Latest reply on Mar 7, 2017 3:00 PM by Joe Oppelt

    Monthly Average

    Teresa Wright

      I am struggling with a way to create a monthly average for 2017 using Quick Table Calculation (Moving Average).  It seems like it should be so simple.  I cannot share data due to security restrictions, so I'll do my best with screen shots.

       

      This is my simple table.  I need to calculate a monthly average based on January and February totals.

       

      Screen shot 1.png

       

      I tried using Moving Average Quick Table Calculation

      Screen shot 2.png

       

      This gave the desired amount for February.  But I want only February to display -- not January.

      Screen shot 3.png

       

      Do I need to change something on the Table calculation?

      screen shot 4.png

       

      So far, I give Tableau a C+.  You can do a lot with it, but Excel is so much easier to use.  I am already tired of all the calculations that are needed for what should be a simple thing.

       

      Thanks for any advice you may have to offer!

        • 1. Re: Monthly Average
          Ivan Young

          Hi Teresa,

          I think your moving average is calculating correctly.  If you only want to display feb or the last available month you can create a calculated field LAST()=0 which you will then drag to filters and set to True.

           

          Regards,

          Ivan

          • 2. Re: Monthly Average
            Jim Dehner

            Hi Theresa

             

            Here is a formula based on Superstore sales data

                    Monthly Average>>  if Avg(DATEPART('month',[Order Date]))=1 then Null else

                      RUNNING_SUM(SUM([Sales]))/Avg(DATEPART('month',[Order Date])) end

             

            You would need to sub in your metrics but this will calculate the average of the ytd sales divided by the number of months

            Let me know if this helps

            Jim

             

            • 3. Re: Monthly Average
              Joe Oppelt

              Teresa Wright wrote:

               

               

              ...

               

              So far, I give Tableau a C+.  You can do a lot with it, but Excel is so much easier to use.  I am already tired of all the calculations that are needed for what should be a simple thing.

               

              ...

               

              Part of your trouble is in that statement.

               

              Tableau is not Excel and doesn't pretend to be.  But when we pretend it is, we paint ourselves into messy corners.

               

              Sometimes the people who struggle most with Tableau are the guru-level users of Excel.

               

               

              Tableau is always a dimensional grid, not a spreadsheet.  What tableau does to one "cell", it does to all cells in your sheet.  (That's why you are still seeing January on your sheet.)  And that's why it seems like you need to do complicated stuff to get what you want.

               

              ---

               

              Having said that, let's look at what you need to do.

               

              The average of two cells actually needs to be looked at as the average of all the cells across that dimension:  in your case, for example, it's the average of all the [Outgoing] cells across the months.  And each of those [Outgoing] values isn't just a single number.  It's a SUM of all the rows that fall in January, and the SUM of all the rows that fall in February.  MOVING_AVG works for you here because you have only two months selected.  If you had three months of data, would you want two different values as the result? (AVG of Jan-Feb, and AVG of Feb-Mar.)  Or would you want one overall average?

               

              I could help you out more if I had a physical example of your data.  I know you have proprietary issues, but we can share anonymized workbooks.  Check out this thread:

               

              Anonymize your Tableau Package Data for Sharing

               

              I don't need all your data.  Just a handful of rows for each month.