Tableau: Calculate Monthly and Yearly Averages from Days

Question

I have a few years of data by day that looks like this:

Date        Count
1/1/2015    1000
1/2/2015    1010
1/3/2015    1040

I would like to create a calculation that allows me to switch between Day, Month and Year using Tableau's date formats without having to calculate the average for each individual format. For instance, the monthly average for Jan 2015 would be the total of Count for all 31 days in January 2015 divided by 31.

Does your data source have exactly one row for each day without gaps? — Alex Blakemore
No, there are extra dimensions that could multiply each date. — ericbrownaustin
But no missing days? And when you have more than one row for the same day, are the Count values the same as if the rows are duplicates? Or are the counts dependent on thos other dimensions? — Alex Blakemore
The counts are completely distinct. No duplication. No missing days. — ericbrownaustin

Alex Blakemore Alex Blakemore · Accepted Answer · 2016-12-29T18:12:17

Since you have no missing data (days) and no duplicate data, the solution is extremely easy.

Place the Count field on any shelf (except the filter shelf). For example, place it on the Rows shelf. Click on the Count field on the Rows shelf and change the aggregation to AVG(). You should see the AVG(Count) of all your data rows at this point.

Place the Date field on any shelf (except the filter shelf). For example, place it on the Columns shelf. Click on the Date field pill on the Columns shelf, and choose the level of granularity you wish from the SECOND batch of choices. (The first batch returns date parts, not dates) For example, choose Month. You'll see the Avg(Count) per month.

Place any other dimensions on shelves to either filter to specific dimension values or to show the average counts broken down by your other dimensions.

Users can drill up or down the granularity of your dates by clicking the + or - icons by the date axis, or by clicking on the Date pill. You can choose to display the dates as a continuous or discrete value.

If you don't like the +/- UI, then you can create a string valued parameter with values: year, quarter, month, day etc, and then create a calculated field using the date_trunc() function to allow, say, a dashboard user to to choose the date granularity from a different type of UI control.

Tableau: Calculate Monthly and Yearly Averages from Days

1 Answers