3 Replies Latest reply on Mar 11, 2013 2:15 AM by Jim Wahl

    Trend line shows all weeks between actual weeks. how to get rid of null weeks?

    Syed Shah

      Hi All,

       

      I have bar graph that shows data for a few weeks. For example 201104 201140 201210 and 2012 50. If I see a simple bar gaph, I see four bars with the relevant measure data. However as soon as I select the trend line for this graph, tableau inserts all weeks between these actual weeks. For example Now I have all weeks on graph even though only these four weeks show the actual bars and the rest of weeks show no bars as they are empty.

       

      My questions is, How can I select the trend line without all those weeks between these four actual weeks being inserted in the graph.

       

      Shah

        • 1. Re: Trend line shows all weeks between actual weeks. how to get rid of null weeks?
          Rafi B

          Hi Shah,

           

          If you don’t mind, would you please attache .twbx file.so that we can procced.

           

          Thanks.

          Rafi.

          • 2. Re: Trend line shows all weeks between actual weeks. how to get rid of null weeks?
            Syed Shah

            Hi,

             

            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

             

            Bar1.PNG

             

            However as soon as I add trend lines, the graph adds 1986 and 1987.

             

            Bar2.PNG

             

            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

             

            Regards

            Shah

            • 3. Re: Trend line shows all weeks between actual weeks. how to get rid of null weeks?
              Jim Wahl

              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"

              movingavg.png

               

               

              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)

              END

              2.2. This will result in two "dots". Connect these by copying (CTRL drag) the table calc to the path shelf.

               

              faked_trend.png

               

              See attached workbook.