Tableau add trend line to discrete data?

0
votes

I have run into this more times then I like, and I think it will continuously haunt me. I am creating reports/dashboards that report monthly or yearly or weekly data. The dates come in with just the last day of the period. For example, now I am working 2019 monthly report that is a bar chart with a trend line. Took me about 15 minutes to make in excel. However, we are trying to move everything into Tableau for dashboards. Trend line is always grayed out when using discrete dates and sometimes when using continuous dates. The dates are in the format DD/MM/YYYY so I can convert them as continuous but that skews the spacing on the X-Axis. I have messed around to get it to work but it takes time. I am shocked that this very basic thing does not work when Excel has been able to do it for a very long time. Does anyone know of a good work around? I have tried calculated the trend line myself, but do not see how I can add in the y=mx+B line that is generated. I am debating creating a data set just for this, but that seems long and hard way for something that I would have expected out of the box. Below is some basic data, in Excel it takes about 1 minutes to create a line chart, click (+) add trend line and your done.

enter image description here

enter image description here

enter image description here

1
tableau-apitrendline
Why do you think having a continuous date axis skews the visualization? BTW, it might help to learn the difference between date parts and truncated dates - both available from a menu, but they are quite different concepts. The fact that one is discrete by default, and the other continuous by default is not the critical distinction. – Alex Blakemore
@AlexBlakemore thanks for your reply. I have added 2 charts to illustrate the skew, the 1st is the standard unedited one. As you will see there are 2 issues, 1st the date in the X-Axis does not align with the Point and 2nd although there are 6 months of data only 5 are displayed. The 2nd picture I formatted the axis to display starting on the 15th of the month, so it keeps all 6 months and almost aligns the data.Looking at Oct19 you will see the data is still miss align. With a bar chart I cannot get the trend line to ever appear. In all cases I am using full dates EOMonth aggregation. – Holmes IV

1 Answers

1
votes

I was not able to deal with the skew of the X-Axis very well but playing with the date it started and setting the major tick marks to monthly got me close. Looking at the picture below you will see the tick marks are not in the center of the bars but close. As for the trend line, I added a Dual axis on the same data that was the sum of all shown fields with no division by the parts (this is a stack mark bar chart). Since this is a basic line chart with continuous dates, a trend line is a simple click. I then removed the Tick Markets and set the opacity to 0% making the line invisible. I did not change the trend line so now that is all you can see. Seems like a long and hard way to do it, but it works. (UPDATE) Better fix, NEVER USE EOM always use First of Month. enter image description here