3 Replies Latest reply on Jul 24, 2012 8:22 AM by Annie Elliott

    need help with a wierd average

    Annie Elliott

      Hi -

       

      I have a dataset that has some dims, one of which is a date, and a measure - a count of X.

       

      Lets call the fields:

       

      date

      dimA

      dimB

      countX

       

      I need to plot the following:

       

      X - Day of Week (Su-Sa)

      Y - Numeric

       

      On the Y axis, I need to plot 2 things with different marks:

       

      1) the average count over the last 7 weeks for that particular day, not including lastest one (eg, avg count of the 6 Sundays *prior* to this past Sunday) as a line

       

      2) the current latest count for a given weekday (eg, this past Sunday, which was not included in the average above) as a point.

       

      Any help is very, very much appreciated.

        • 1. Re: need help with a wierd average
          Jonathan Drummey

          Hello Annie,

           

          Attached is a workbook showing one way to create this view using the Superstore Sales data, using a count of orders as the measure, and Order Date for the date.

           

          The workbook uses the following calculated fields:

           

          Weekday of Order - a field with the formula DATENAME('weekday',[Order Date]) to allow us to partition the table calculations by the weekday.

          Week Filter - when set to filter on True on the Filter shelf, only returns the last 7 weeks of data, to improve performance by reducing the number of rows that Tableau is retrieving from the DB.

          Latest Result Filter - when set to filter on True on the Filter shelf, this table calculation filter "hides' what would be overlapping rows, the Compute Using is set to Order Date.

          Last 7 Weeks Orders - a table calculation that calculates the average for the last 7 weeks, with the Compute Using also set to Order Date.

           

          The Crosstab worksheet shows the results with the filters applied, then we can create a view with Measure Values on Rows, Measure Names on the Color shelf, WEEK(Order Date) on the Level of Detail and the Weekday of Order on Columns.

           

          Hope this helps!

           

          Jonathan

          • 2. Re: need help with a wierd average
            Annie Elliott

            Ok. I'm working through this, but it is not quite behaving as expected. As soon as I am sure it's not me making an obvious mistake, I will post my closest try.

            • 3. Re: need help with a wierd average
              Annie Elliott

              FOLLOWUP: I ended up doing this in SQL. I could not get the clean graph I wanted doing it in Tableau, as I had to drag extra fields over.