-
1. Re: Dotted Line Charts
Tyler Garrett Dec 5, 2017 1:29 PM (in response to Anthony Cua)2 of 2 people found this helpfulDid 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
-
2. Re: Dotted Line Charts
Rahul Singh Dec 5, 2017 11:03 AM (in response to Anthony Cua)-
Forecasts.twbx 29.9 KB
-
-
3. Re: Dotted Line Charts
Tyler Garrett Dec 5, 2017 2:00 PM (in response to Anthony Cua)2 of 2 people found this helpful1. 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
5. Select your date value
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
-
4. Re: Dotted Line Charts
Anthony Cua Dec 5, 2017 2:18 PM (in response to Anthony Cua)1 of 1 people found this helpfulThank 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?
-
5. Re: Dotted Line Charts
Tyler Garrett Jun 20, 2019 8:15 AM (in response to Anthony Cua)2 of 2 people found this helpfulNo worries, I love learning and I'm sharing as I learn.
Will take a crack at this later
Best
Tyler
Dev3lop
-
6. Re: Dotted Line Charts
Tyler Garrett Dec 5, 2017 2:37 PM (in response to Anthony Cua)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 Dec 5, 2017 2:50 PM (in response to Tyler Garrett)1 of 1 people found this helpfulHi again Tyler.
I'm basically getting the actual sales, and the forecast sales from 2 separate excel files. Does that help our cause?
-
8. Re: Dotted Line Charts
Tyler Garrett Dec 5, 2017 2:54 PM (in response to Anthony Cua)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 Dec 5, 2017 3:09 PM (in response to Tyler Garrett)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.
-
Forecast XLSX.zip 14.8 KB
-
-
10. Re: Dotted Line Charts
Peter FakanDec 5, 2017 3:11 PM (in response to Anthony Cua)
1 of 1 people found this helpfulHi 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
-
11. Re: Dotted Line Charts
Anthony Cua Dec 5, 2017 3:24 PM (in response to Peter Fakan)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 Dec 5, 2017 4:23 PM (in response to Anthony Cua)Dual axis will not work for this
I'm almost done with the sql, 1 sec.
-
13. Re: Dotted Line Charts
Tyler Garrett Jun 20, 2019 8:14 AM (in response to Anthony Cua)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
-
TylerG.SQL.zip 251 bytes
-
Table3.xlsx 12.2 KB
-
-
14. Re: Dotted Line Charts
Anthony Cua Dec 5, 2017 6:59 PM (in response to Tyler Garrett)1 of 1 people found this helpfulThis 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!