1 2 Previous Next 15 Replies Latest reply on Jun 20, 2019 8:14 AM by Tyler Garrett

# Dotted Line Charts

Hi!

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

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

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.

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

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

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

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

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

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...

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

4. Click Specific Dimensions

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.

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.

*mac ^

Best,

Tyler

Dev3lop.com

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

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?

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

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

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

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

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

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

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

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

Dual axis will not work for this

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

• ###### 13. Re: Dotted Line Charts

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]

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

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.