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

need help with a wierd average

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

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

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

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.