12 Replies Latest reply on Aug 21, 2013 7:44 AM by .Yann Treguier

# How to create previous month avg line in current month daily trending

I want to be able to have a straight line across the chart(like a reference line) representing the avg daily sales for the previous month (Nov 2013).  Based on the database values that line should be right at \$53,934.  I was thinking a table calc will do that, but the chart time axis is displaying days while the table calc needs to compute around month....

• ###### 1. Re: How to create previous month avg line in current month daily trending

Yann,

The table calcs for this one get a little complex.  I ended up using two table calculations.

1. Monthly Average

WINDOW_AVG(SUM(Sales))

It is calculated along Order Date at the deepest level, restarting every month.

2. Previous Monthly Average

LOOKUP([Monthly Average], -1)

It looks up the previous month's average.  It is calculated along Order Date at the Month level.

Regards,

Joshua

• ###### 2. Re: How to create previous month avg line in current month daily trending

By the way, you can actually use a reference line if you want.  Just move the calculated field to level of detail and then add a reference line using the value of the calculation.

--Joshua

• ###### 3. Re: How to create previous month avg line in current month daily trending

Hi Joshua,

Very nice solution. A couple of questions:

- Why did you set up the Year filter as LOOKUP(YEAR(ATTR([Order Date])), 0) instead of using the default dimension Year([Order Date])? The latter seems to work as well.

- Why does it require discrete dates? I tried adding discrete dates to the level of detail (for the table calc) while keeping a continuous dimension Day([Order Date]) in the columns shelf, but it didn't work.

Thanks,

Pedro

• ###### 4. Re: How to create previous month avg line in current month daily trending

Hi Joshua, thanks for looking into this.  Maybe I was not clear about the requirements I have.  I cannot have year or month on the shelves.   The chart is set to look at x number of days back (driven by the parameter) and must remain as shown in my original post.  That is where the challenge is.  How do you compute a table calc around months when the months cannot be in view....

Thanks

Yann

• ###### 5. Re: Re: How to create previous month avg line in current month daily trending

Yann,

See if this is closer.  Basically, I just went back to using your filter and hid the header for the year and month.  The fields themselves do need to be in the view for the table calc.  However, they don't have to be shown.

Regards,

Joshua

• ###### 6. Re: Re: How to create previous month avg line in current month daily trending

Pedro,

Good questions!  Let me see if I can give answers:

1. using LOOKUP(YEAR(ATTR([Order Date])), 0) as a table calc filter allowed the filter to be applied after other table calculations were done.  Since the previous month average had to be calculated using values for the previous month, I needed those values to be part of the data set returned from the data source.  A regular filter would have excluded the values and the previous month average would not be calculated.  If I don't want to show the month, the table calc filter allows me to "hide" it without excluding the values from other table calculations.

2. Table calculations do require discrete values for addressing and partitioning.  They cannot be addressed or partitioned on continuous values.  The trick with dates (the built-in hierarchy) is that when you specify "deepest" as the addressing it is going to use the deepest on Rows or Columns.  The same is true with whatever part of the date is specified as the "Restart every..."

Regards,

Joshua

• ###### 7. Re: Re: Re: How to create previous month avg line in current month daily trending

Hi Joshua,

Nice work! I've got a couple of comments:

#1. One thing that I'll do for workbooks like this where we need N days/weeks/months prior is to add a relative date filter that gets me N+X in the past, where X is a factor to adjust for cases where there might be late data. (For example, I might do last 3 months in this case). That way instead of querying all the data to run table calcs on, Tableau is only returning enough to generate the right results, then the table calc filter reduces the displayed results.

#2. is not an accurate statement. Table calculations require *dimensions* for addressing and partitioning. The discrete vs. continuous for a dimension can affect what sort of padding Tableau might do. I set up a simple example in the attached in the "continuous demo" worksheet.

For the averages for previous month, I also set up an example using a continuous datetrunc version of month & day. The view is plotted by an aggregate of the day, with both Month and Day on the Level of Detail Shelf. This is necessary because of an undocumented aspect of At the Level where it either doesn't work (this case) or results in unnecessary padding when the addressing dimension used for At the Level is on the Marks Card while other addressing dimensions are on Rows and/or Columns. (Ordinarily, I'd build this view with Day on Columns and Month on the Level of Detail, and that's where At the Level blows up, so we solve that by putting all the dimensions on the Level of Detail and then using an aggregate of the dimension to generate the view).

Does this make sense?

Jonathan

• ###### 8. Re: Re: Re: How to create previous month avg line in current month daily trending

Jonathan,

Thank you very much for the correction on #2.  That does make sense.  Is the "undocumented aspect of At the Level" considered a bug or simply an anomalous behavior?

Regards,

Joshua

• ###### 9. Re: Re: Re: How to create previous month avg line in current month daily trending

From what I've been told, having At the Level on a dimension on the Marks Card when other addressing dimensions are on Rows/Columns was not designed for, so it's a bug or a feature, depending on how you look at it.

• ###### 10. Re: How to create previous month avg line in current month daily trending

Thank you Joshua for putting this together.  Jonathan, thank you as well for your contribution.  I was looking at your "continuous avg view" example and noticed the break in the line between the nov 30 and Dec 1st data points.  Why is that happening? There is obviously no Nov 31 data point and the month is not in view which would create a pane break (although if I am not mistaken, Tableau 8 allows to keep lines continuous across time panes).

• ###### 11. Re: How to create previous month avg line in current month daily trending

One more things if you guys could help with.  I want to create a previous 3 mth avg reference line (just like the previous month avg ref line).  I don't think the LOOKUP can be used any more so I was trying the WINDOW_AVG(SUM(Sales),-3,0) but I can't figure out how to make it compute along the months instead of the days.

Thanks!

• ###### 12. Re: How to create previous month avg line in current month daily trending

Hi Jonathan, I had one more question which I addressed to Jonathan but he might not be able to get to it.  Would you by any chance be able to help?  Check my post from Aug 20, 2013 3:57 PM .  Thanks!