Have you ever run into the problem of not being able to show a trend line on a time series chart? Typically when creating trend charts over time the trend line will appear without problem. When using the pre-defined date options of MDY or MY, Tableau converts your dates in a unique fashion eliminating the option of creating trend lines. This article will explain the process to get a trend line to appear for all date combinations and hierarchies.
Other situations it may seem to work, but you get this error:
Typically you need two quantitative axes or one quantitative axis and one ordinal axis that support uniform quantitative interpretation.
Taking a look at this chart it would make sense that a trend could be computed. When we take a closer look at how the dates are being broken up we notice that it is not a continuous axis.
Creating Datetrunc Calculation
A datetrunc function will roll your dates up to a specific date part. For example DATETRUNC(‘month’, #August 27, 2010#) changes the date to August 1, 2010. Now that we understand what datetrunc function does let’s create one.
Here is an example of how to break up your date field into months using the datetrunc function:
Once we have the data rolled into one specific month we can plot this new field showing all values and create a trend line for it. Here are the detailed steps to achieve this:
Creating Visualization Using Datetrunc Calculation
Step 1
Drag your datetrunc field over to columns.
Step 2
Right click your new datetrunc field and select all values.
Step 3
Double click on your measure.
Step 4
Right click on your view and select trend lines.
The end result should look like this.
