If you don’t mind, would you please attache .twbx file.so that we can procced.
Its very simple. The trend line adds the missing time in the graph and I dont want that. For example, have a look at the below simple graph. As you can see, After 1985, you see 1988
However as soon as I add trend lines, the graph adds 1986 and 1987.
I believe this is a default behavior of trend lines. My questions is show should I force the graph not to show the extra 1986 and 1987 but select trend lines
Depends on your goal, but a trend line may not be the best tool.
The problem you're seeing is that least square trend lines are only valid for continuous data. Even if Tableau allowed you to hide the years with nulls, the result would be (should be) a jagged, discontinuous line. Say you had five values
date: value: regression-line value
2001 2 1.8
2005 4 4.5
2006 5 5.2
2007 6 6
2008 7 6.8
Plotting these without the 2002 to 2004 years causes the to "jump" from 2001 to 2005 and then flatten for the remaining years.
So that's the problem. Possible solutions:
1. Goal is forecasting:
Perhaps a moving average is a better estimator. To get a line on top of your bar graph, you'd
1.1 Add count to Rows shelf; you should see a duplicate of your bar graph.
1.2. On the second count pill, select dual axis.
1.3. On marks shelf, select Multiple Mark Types > Arrow over to the first "count" > Select Bar. Arrow over again to the second "count" and select line.
1.4. Add table calc. On the second count pill, select > Quick Table Calculation > Moving Average. You can go back and edit the table calculation to include more than the default two previous values in the average calculation.
1.5 Click on second count pill and make sure synchronize axis is checked. You can also uncheck "Show Header"
2. I just want a line?
You can "fake" a trend line by calculating a starting and end point and connecting these.
2.1 Starting from 1.4 above, instead of choosing Quick Table Calc, choose Add table calculation > Customize and use the following code to create two data points resulting from an average of the first and last five values.
IF FIRST() == 0 THEN
WINDOW_AVG(SUM([count]), 0, 5) //Average first five values to get starting point
ELSEIF LAST() == 0 THEN
WINDOW_AVG(SUM([count]), -5, 0)
2.2. This will result in two "dots". Connect these by copying (CTRL drag) the table calc to the path shelf.
See attached workbook.
trendlines.twbx.zip 37.0 KB