3 Replies Latest reply on Mar 7, 2017 3:00 PM by Joe Oppelt

Monthly Average

I am struggling with a way to create a monthly average for 2017 using Quick Table Calculation (Moving Average).  It seems like it should be so simple.  I cannot share data due to security restrictions, so I'll do my best with screen shots.

This is my simple table.  I need to calculate a monthly average based on January and February totals.

I tried using Moving Average Quick Table Calculation

This gave the desired amount for February.  But I want only February to display -- not January.

Do I need to change something on the Table calculation?

So far, I give Tableau a C+.  You can do a lot with it, but Excel is so much easier to use.  I am already tired of all the calculations that are needed for what should be a simple thing.

Thanks for any advice you may have to offer!

• 1. Re: Monthly Average

Hi Teresa,

I think your moving average is calculating correctly.  If you only want to display feb or the last available month you can create a calculated field LAST()=0 which you will then drag to filters and set to True.

Regards,

Ivan

• 2. Re: Monthly Average

Hi Theresa

Here is a formula based on Superstore sales data

Monthly Average>>  if Avg(DATEPART('month',[Order Date]))=1 then Null else

RUNNING_SUM(SUM([Sales]))/Avg(DATEPART('month',[Order Date])) end

You would need to sub in your metrics but this will calculate the average of the ytd sales divided by the number of months

Let me know if this helps

Jim

• 3. Re: Monthly Average

Teresa Wright wrote:

...

So far, I give Tableau a C+.  You can do a lot with it, but Excel is so much easier to use.  I am already tired of all the calculations that are needed for what should be a simple thing.

...

Part of your trouble is in that statement.

Tableau is not Excel and doesn't pretend to be.  But when we pretend it is, we paint ourselves into messy corners.

Sometimes the people who struggle most with Tableau are the guru-level users of Excel.

Tableau is always a dimensional grid, not a spreadsheet.  What tableau does to one "cell", it does to all cells in your sheet.  (That's why you are still seeing January on your sheet.)  And that's why it seems like you need to do complicated stuff to get what you want.

---

Having said that, let's look at what you need to do.

The average of two cells actually needs to be looked at as the average of all the cells across that dimension:  in your case, for example, it's the average of all the [Outgoing] cells across the months.  And each of those [Outgoing] values isn't just a single number.  It's a SUM of all the rows that fall in January, and the SUM of all the rows that fall in February.  MOVING_AVG works for you here because you have only two months selected.  If you had three months of data, would you want two different values as the result? (AVG of Jan-Feb, and AVG of Feb-Mar.)  Or would you want one overall average?

I could help you out more if I had a physical example of your data.  I know you have proprietary issues, but we can share anonymized workbooks.  Check out this thread:

I don't need all your data.  Just a handful of rows for each month.