10 Replies Latest reply on Jun 14, 2012 3:38 PM by Michael Cristiani

    Segmented trendlines

    John Munoz

      Hi Folks,

       

      I need Tableau to compute trendlines within each group's range. For example, the X axis range for the blue group is roughly -.8 to zero. And the red group is roughly 0 to 1. I want the red trendline to stay on the positive side of the axis only, and the blue trendline to stay on the negative side of the x axis. And each trendline should be calculated over the range for each group, not across the range for both groups. I've posted an example at the bottom of this message (made by someone else in R, not Tableau) that shows what I'm trying to get to.

       

      I've attached the workbook that got me to the viz below. I've tried to segment each group using the ranges above and then tried to constrain the trendlines that way, but the single x axis prevented that from working.

       

      Thanks!

       

      segment trendlines.PNG

      r version.png

        • 1. Re: Segmented trendlines
          Jonathan Drummey

          Hi John,

           

          I've attached a workbook with a couple of ideas. One splits the view into two worksheets and then re-merges them on a dashboard, the second uses a dual axis where the secondary axis is the trendline; however, I don't know enough statistics to make Tableau draw the right trendline, you'd need to set that up yourself or get help from someone else.

           

          Jonathan

           

          PS: When you're using the Color shelf to separate trendlines as you are, my understanding is that Tableau is doing separate computations for each color, so I'm not sure why you'd be seeing different lines in R vs. Tableau.

          1 of 1 people found this helpful
          • 2. Re: Segmented trendlines
            Shawn Wallwork

            John, interesting data set. I think combining them on a dashboard as Jonathan suggested is best. You can improve, or at least better match the trend line of the original, if you bump the polynomial degrees up to 4. You still get the wild tails at the ends, but you can add the confidence bands in to indicate these aren't statistically viable. Here's what it would look like:

             

            Trendlines.JPG

            Workbook attached.

             

            A few things I found interesting about the results:

             

            1. The average Democrat is not as liberal (41%) as the average Republican is conservative (47%).
            2. The most extremely liberal Democrat, Rep. Barbara Lee (74%), is not as extreme as the most extremely conservative Republican, Rep. Jeff Flake (99%)
            3. The average grade level of speech-making between the two parties isn't that much different (11.67 vs. 11.22)
            4. There is no 'moderate middle' and it's going to get worse as the most moderate member of each party has announced their retirement:

             

            Moderate Middle.JPG

             

            Fascinating stuff John. When you're finished with your viz I'd really like to see it.

             

            --Shawn

            1 of 1 people found this helpful
            • 3. Re: Segmented trendlines
              Joe Mako

              Another option is to use a dual axis, as in the attached.

               

              I suspect it would be possible to perform a polynomial regression in Tableau with table calcs, I just have not taken the time to work it out. So as an example I just used the formula the trend line created.

               

              This may not be ideal for less dense data.

              • 4. Re: Segmented trendlines
                John Munoz

                You are a master Joe! Genius move using the formula for the trendline as a calculated field.

                 

                This is perfect!

                • 5. Re: Segmented trendlines
                  John Munoz

                  Thanks Shawn. Great idea creating a quadrant for each party. I think I'm going to use that one in my final viz.

                   

                  BTW, I'd love to take credit for thinking of this analysis, but I can't. The data came from the Sunlight foundation and the original analysis, with the Viz I'm trying to improve, came from a story at at http://sunlightfoundation.com/blog/2012/05/21/congressional-speech/

                   

                  I think their scatterplot would be more valuable if it had some points labeled and interactivity for filtering. The original plot was done in R and I'm guessing that adding Tableau-like interactivity to R is next to impossible.

                   

                  Anyway, I hope to get this post up on my blog (http://www.bizintelguru.com/blog) sometime tonight and I will definitely mention the great and generous work you, Joe, and Jonathan offered up on making the viz work! Thanks.

                  • 6. Re: Segmented trendlines
                    Shawn Wallwork

                    One other improvement you might consider is adding the Rep./Sen. home state to the tool tips. I'm certainly not up on politics enough to know where Rep Lucille Roybal-Allard is from for instance -- would be nice to know.

                     

                    --Shawn

                    1 of 1 people found this helpful
                    • 7. Re: Segmented trendlines
                      John Munoz

                      Shawn, Jonathan, and Joe,

                       

                      Thanks again for your help with this problem. I've posted the viz, and a discussion about the original one, at my blog at, http://bit.ly/L7ezNd I hope you take some time to see how I used your expert advice.

                       

                      Take care,

                       

                      John Munoz

                      www.bizintelguru.com

                      • 8. Re: Segmented trendlines
                        Michael Cristiani

                        Shawn, and the rest of you dudes,

                         

                        Taking up Shawn's challenge on the State and Rep/Sen thingie, here are my feeble $0.02 worth.  Self explanatory.

                         

                        http://public.tableausoftware.com/views/GradelevelofCongressspeakersbyideology_0/Dashboard2?:embed=y

                         

                        MANY BLESSINGS!

                        Michael

                        1 of 1 people found this helpful
                        • 9. Re: Segmented trendlines
                          John Munoz

                          Michael,

                           

                          I feel like I left the door open to my house and you just came in and redecorated the place, taking it from a bachelor pad to a King's palace. Nice work!

                           

                          One small change from what you put out there. Rather than using alphabetic ordering for states, what do you think about ranking the states by average (could use median too) grade level of speech? See my ranking, with reference marks at:

                           

                          http://public.tableausoftware.com/views/GradelevelofCongressspeakersbyideologysortstate/Dashboard2?:embed=y

                          • 10. Re: Segmented trendlines
                            Michael Cristiani

                            John,

                             

                            Neat, really neat.  Viewers and interactors can really get the sense of things without hardly and cognitive load, and the interaction is rich and inviting.  You may want to set the dot plot to Fit Width so there is no horizontal scrolling.

                             

                            So here is an added question this added wrinkle facilitates:  in states where the delegation has a number of newcomers, wouldn't it be interesting to plot their predecessors and see what kind of shift there has been, especially if the seats changed parties.  For example, Texas.  Looks like the Democrats for the most part are long time servers, But were there any Democrats unseated by new Republicans, and how do their liberal/conservative and grade level speech compare?

                             

                            Thanks for doing this, John. Lots of fun learning from you masters.

                             

                            MANY BLESSINGS!

                            Michael