3 Replies Latest reply on Oct 3, 2012 6:42 PM by Jonathan Drummey

    Running Average and Blank Cells

    Jay Rosenthal

      I'd love some help with a running total calculation. I'm trying to do a running weekly average calculation with data that looks like this:



      Weekly Sales
      YTD Weekly Average
      YTD Total
      $40  This cell is calculation $80/2 instead of $80/3$80


      The problem is that blank cells are skewing the YTD weekly average by not counting the additional week in the denominator.


      My raw data is at the 'deal' level with a close date.  I use the close date to create rows of Year, Month, Week and then corresponding sums of all sales.  My formula is RUNNING_AVG( sum([salesamt])).  I'm computing using advance settings with year, month, week, and region.


      Does anyone know how to get the denominator to be right?


      Thanks for the help!

        • 1. Re: Running Average and Blank Cells
          Mark Holtz

          Rather than the quick running functions Tableau offers you, you might want to create your own Calculated Field that does a running sum / specific count:


          RUNNING_SUM(SUM([Sales])) / [Week]


          I'm not sure if your Week field is a DateField and you're SHOWING week (e.g., 1/5/2012 would show Week 1, 1/17/2012 would show Week 3) or if it's actually a field in your data that shows "3"


          If it's a Date field, you would want to use DATEPART('week',[DateField]) instead of [Week] in the formula above.


          Hope that helps!

          • 2. Re: Running Average and Blank Cells
            Jay Rosenthal

            Thanks for the help!


            The underlying dataset has no row in the spot in question.  So I can't seem to get the denominator to be a "3." I tried doing the calculation the way suggested above using max(datepart('week',[closedate])) to get the week # and the doing division.  However, in the category in question since there is no row for Midwest sales in week 3 it's using the week 2 number for division.


            Any other thoughts on this are appreciated. Thanks.

            • 3. Re: Running Average and Blank Cells
              Jonathan Drummey



              Tableau pads out dates across dimensions - "pads the domain" is the term the developers use - when table calculations are in use, which is why you're even seeing a row for Midwest on 1/15. But it only pads and generates results for table calculations. So, if you wrap the DATEPART('week',[DateField]) that Mark suggested inside a table calculation that will return a result for every row in the partition - such as WINDOW_MIN(ATTR(DATEPART('week',[DateField]))) - then you can get a week number that you can use for the divisor. I set this up in the attached. Note that the YTD Weekly Average is a nested table calc, with Compute Using set to Date for the YTD Total and Region for the the YTD Weekly Average.