1 2 Previous Next 15 Replies Latest reply on Dec 5, 2017 7:08 PM by Tyler Garrett

    Dotted Line Charts

    Anthony Cua

      Hi!

       

      I want to be able to create the chart below in Tableau.

      forecasts.png

       

       

      Unfortunately, I cannot find a way to do dotted lines for 2 of my measures. Help please.

      (TWBX attached for reference.)

       

      Regards,

      Tony

        • 1. Re: Dotted Line Charts
          Tyler Garrett

          Did you try a dual axis and instead of Line, you use circles stacked on it? That's one way.

           

          I'm sure you did that. Here's the next way.

           

          Drag and drop DATE on your PAGES. Then open the settings.

           

          Screen Shot 2017-12-05 at 12.48.22 PM.png

          You're going to want to play around with this box called 'Show History'

          Screen Shot 2017-12-05 at 12.48.29 PM.png

          Screen Shot 2017-12-05 at 12.46.59 PM.png

           

          Or try this calc and add to detail

           

          if last()=0 then 1

          elseif first()=0 then 2

          else 0

          end

           

          Best,

          Tyler

          Dev3lop.com

          2 of 2 people found this helpful
          • 2. Re: Dotted Line Charts
            Rahul Singh

            Hi Tony,
            Id this something that you want ?

             

            Attaching the workbook as well

             

            Thanks
            Rahul Singh

            1 of 1 people found this helpful
            • 3. Re: Dotted Line Charts
              Tyler Garrett

              1. dual axis two measures

              2. one is a circle the other a line.

              Here's the view without coloring the LINE by company, also explaining we are on to something

              Screen Shot 2017-12-05 at 3.35.32 PM.png

              Mark card for circle::: splitter calc explained below.

              Screen Shot 2017-12-05 at 3.41.21 PM.png

               

              1. Make a calculation:

              if last()=0 then 1

              elseif first()=0 then 2

              else 3 //color WHITE to hide pieces of the line.

              end

              Will offer a way to COLOR your 'circles' - don't use a string because that will make this a slow calculation, also will be a bad practice to get into.

              2. Drag it on to color for your CIRCLE mark card.

              3. Right click splitter calc, select edit table calc...

              Screen Shot 2017-12-05 at 3.51.10 PM.png

              Now you will see a window with a couple options..

              4. Click Specific Dimensions

              5. Select your date value

              Screen Shot 2017-12-05 at 3.52.12 PM.png

              Coloring the lines...

              1. Using the company for detail to break the line in pieces

              2. Make sure the 3 is colored WHITE.

              end - And that's really all you need to do to generate that break in the line.

               

              Screen Shot 2017-12-05 at 3.40.43 PM.png

              Getting to the white color is very quick and painless.

              1. open the color legend

              2. double clicking on the color in the edit colors window will open up a menu

              3. select white or whatever your background color is.

              Screen Shot 2017-12-05 at 3.38.24 PM.png

              *mac ^

               

              Best,

              Tyler

              Dev3lop.com

              2 of 2 people found this helpful
              • 4. Re: Dotted Line Charts
                Anthony Cua

                Thank you very much for the response tyler garrett and Rahul Singh!

                 

                I went through all of your solutions, and I feel sorry that I wasn't able to be more specific about my needs.

                 

                I'd like the two sales lines to be solid, and I'd like the two forecast lines to be dotted. (I removed the data markers in my excel chart to illustrate this more clearly.) Is there a way to do this?

                 

                forecasts 2.png

                1 of 1 people found this helpful
                • 5. Re: Dotted Line Charts
                  Tyler Garrett

                  No worries, I love learning and I'm sharing as I learn.

                   

                  Will take a crack at this later

                   

                  Best

                  Tyler

                  Dev3lop

                  2 of 2 people found this helpful
                  • 6. Re: Dotted Line Charts
                    Tyler Garrett

                    Depending on how the data is coming into the product, I can offer a data transformation - and then utilize the above methods to generate this 'view' which I'm assuming is coming from another data product or third party app?

                     

                    If you're bringing down these forecast lines, and the other two lines, it would be possible to utilize a stacking method. Which would require us to dimension'alize measures.

                     

                    So - let us know what the data format is - and go from there.

                     

                    Best,

                    Tyler

                    • 7. Re: Dotted Line Charts
                      Anthony Cua

                      Hi again Tyler.

                       

                      I'm basically getting the actual sales, and the forecast sales from 2 separate excel files. Does that help our cause?

                      1 of 1 people found this helpful
                      • 8. Re: Dotted Line Charts
                        Tyler Garrett

                        HECK YES. Big time. Especially if you're not being forced to generate the forecast in Tableau - that would be something I'd have to bow out, lol.

                         

                        Okay so if you just give me the table structure, I can explain what to do

                         

                         

                        What are the headers? Or if you could turn it into SQL...

                         

                        Select

                        measure1,

                        date,

                        measure2,

                        from X

                         

                        Let me know, and that's all we need to progress this.

                        • 9. Re: Dotted Line Charts
                          Anthony Cua

                          Ohh... In that case, you'd be happy to know that my databases (as well as the linkage) are simple.

                          I'm attaching 2 excel files mimicking select database columns. Links are based on date and campaign name.

                          • 10. Re: Dotted Line Charts
                            Peter Fakan

                            Hi Anthony,

                             

                            Have you tried creating a dual-axis chart and putting the 2 measures you wish to be 'dotted' on the second axis ?

                             

                            This will give you the viz you are looking for because you can edit the format of the lines on the second axis and preserve the solid lines on the first axis.

                             

                            HTH

                             

                            Peter

                            1 of 1 people found this helpful
                            • 11. Re: Dotted Line Charts
                              Anthony Cua

                              Hi Peter!

                               

                              I was able to create a dotted line chart for my secondary axis, yes. But, I can only get 1 measure in my secondary axis. (I remember trying to Google, and seeing a post where it said that Tableau can only handle 1 measure in the secondary Y-axis.)

                               

                              Is there now a way to throw 2 measures in that secondary axis?

                               

                              Regards,

                              Tony

                              • 12. Re: Dotted Line Charts
                                Tyler Garrett

                                Dual axis will not work for this

                                 

                                I'm almost done with the sql, 1 sec.

                                • 13. Re: Dotted Line Charts
                                  Tyler Garrett

                                  Based on this data structure, I'm going to make some of these field names a bit easier for me to quickly workthrough.

                                   

                                  For the sake of my personal sanity here's how I'm going to re-name it. Attached file + SQL with new naming conventions.

                                  Sales A = SalesA

                                  Sales B = SalesB

                                  Forecasted Sales A = FSalesA

                                  Forecasted Sales B = FSalesB

                                  Campaign = C

                                  Date = D

                                   

                                  Also Sheet1=XSales

                                  And Sheet1=FXSales

                                   

                                  And add sheet1 OR FXSales Sheet -> to the same workbook as the XSales table. (see attached)

                                   

                                  We need those to generate SQL - SQL that doesn't look confusing

                                   

                                  Windows only for CUSTOM SQL to EXCEL:

                                  and if you have MAC, like me... You can't progress with this.

                                  So, manipulate this SQL to fit your Database SYNTAX if that's the case, if windows... Kick back, this should work quickly.

                                  If you have a DBA, he/she will be happy to add this as a view/table, and will edit the SQL for you, they will be happy you have it ready.

                                   

                                  SQL IS KING

                                   

                                  SELECT '1'    AS 'f1',

                                         '5'    AS 'f2',

                                         [xsales$].[d],

                                         [xsales$].[c],

                                         salesa AS Measure1

                                  FROM   [xsales$]

                                  UNION ALL

                                  SELECT '2'    AS 'f1',

                                         '5'    AS 'f2',

                                         [xsales$].[d],

                                         [xsales$].[c],

                                         salesb AS Measure1

                                  FROM   [xsales$]

                                  UNION ALL

                                  SELECT '3'     AS 'f1',

                                         '6'     AS 'f2',

                                         [fxsales$].[d],

                                         [fxsales$].[c],

                                         fsalesa AS Measure1

                                  FROM   [fxsales$]

                                  UNION ALL

                                  SELECT '4'     AS 'f1',

                                         '6'     AS 'f2',

                                         [fxsales$].[d],

                                         [fxsales$].[c],

                                         fsalesa AS Measure1

                                  FROM   [fxsales$]

                                   

                                  TABLEAU USAGE.

                                  Build a 2 calculations for two measures:

                                   

                                  1)

                                  if 'f1'=5 then [Measure1]

                                  end //generates your SALES measure

                                   

                                  2)

                                  if 'f1'=6 then [Measure1]

                                  end //generates your Forecast Sales measure

                                   

                                  3)

                                  Dual axis these two calculations.

                                   

                                  4)

                                  Drag and drop [f2] into detail on the ALL MARKS card.

                                   

                                  5)

                                  Swap your circles to circle, and line to line.

                                   

                                  What is f2? This will generate the granular detail necessary to connect the lines, and Color the lines/dots.

                                  What is f1? The difference between 'dots' and 'lines' PER query.

                                   

                                  Can i do this with only f2? Yes but your calcs get more complex.

                                  Why are you using numbers? Because they are faster remove the quotes and enjoy.

                                   

                                  This may seem a bit confusing because I'm using integers. okay, okay... Here's how to do it with description values. Dots are circles, but dots is a smaller word to type, lol.

                                  SELECT 'Line1'    AS 'flag1',

                                         'lines'    AS 'flag2',

                                         [xsales$].[date],

                                         [xsales$].[campaign],

                                         salesa AS Measure1

                                  FROM   [xsales$]

                                  UNION ALL

                                  SELECT 'Line2'    AS 'flag1',

                                         'lines'    AS 'flag2',

                                         [xsales$].[date],

                                         [xsales$].[campaign],

                                         salesb AS Measure1

                                  FROM   [xsales$]

                                  UNION ALL

                                  SELECT 'Dot1'     AS 'flag1',

                                         'dots'     AS 'flag2',

                                         [fxsales$].[date],

                                         [fxsales$].[campaign],

                                         fsalesa AS Measure1

                                  FROM   [fxsales$]

                                  UNION ALL

                                  SELECT 'Dot2'     AS 'f1',

                                         'dots'     AS 'f2',

                                         [fxsales$].[date],

                                         [fxsales$].[campaign],

                                         fsalesa AS Measure1

                                  FROM   [fxsales$]

                                   

                                  2cents:

                                  Sometimes 'requirements' generate a TON of work to get there, often times I always ask, 'was this necessary' could we have just used different colors? But sometimes the chart proliferates a deeper understanding of the data coming into Tableau, which is something I'm overly passionate about.

                                   

                                  ending notes:

                                  Union all only works if you:

                                  1. stack similar columns on each other

                                  2. same names

                                  3. same data types

                                  4. if you're having fun

                                   

                                  If you need help beyond this, please contact me on my site in the signature. And we can work through it one on one.

                                   

                                  Best,

                                  Tyler

                                  Dev3lop

                                  2 of 2 people found this helpful
                                  • 14. Re: Dotted Line Charts
                                    Anthony Cua

                                    This is awesome Tyler! Unfortunately, I know nothing about SQL so I'll just pass this on to our DBA. Nevertheless, I really appreciate the work (and more importantly, the tutorial) that you laid here.

                                     

                                    Have a great week ahead!

                                    1 of 1 people found this helpful
                                    1 2 Previous Next