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:

       

       

      WeekRegion
      Weekly Sales
      YTD Weekly Average
      YTD Total
      1East$10$10$10
      1Midwest$20$20$20
      1West$30$30$30
      2East$15$12.5$25
      2Midwest$60$40$80
      2West$80$55$110
      3East$110$45$135
      3Midwest
      $40  This cell is calculation $80/2 instead of $80/3$80
      3West$50$53.3$160

       

      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

              Hi,

               

              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.

               

              Jonathan