3 Replies Latest reply on Mar 2, 2017 7:42 AM by Joshua Milligan

# Two date fields on one combined graph

I am trying to combine Spend and Savings of a project on one graph.  The issue is that the savings will start after the project ends.

 Project Start Year Start Quarter Duration in Months Cost Savings a 2017 3Q 6 \$10,000.00 \$3,000.00 b 2017 4Q 10 \$7,500.00 \$2,400.00 c 2018 1Q 4 \$45,000.00 \$27,000.00 d 2018 2Q 8 \$18,500.00 \$6,000.00 e 2019 3Q 2 \$32,750.00 \$7,400.00
• ###### 1. Re: Two date fields on one combined graph

Bryan,

One approach would be to reshape your data so that you have rows that are for Cost and duplicate rows that are for Savings, so that you can have a single date that applies to both.  One way you could do this in Tableau (as it looks like you're using Excel; though Tableau 10.2 - out just last night! - is able to union other database sources too ) is to create a union of the same data:

with that you'll end up with twice as many rows, but you'll also have the Sheet / Table Name fields to sort things out:

So, I'll treat Costs + Savings as Costs and Costs + Savings1 as Savings.

Then, I'll create a few calculated fields:

1. Date based on the Start Year and Start Quarter:

CASE [Start Quarter]

WHEN "1Q" THEN 0

WHEN "2Q" THEN 3

WHEN "3Q" THEN 6

WHEN "4Q" THEN 9

END,

DATEADD('year', [Start Year] - 1900, #1/1/1900#))

2. Actual Date which gives me either the start date or the start date + duration based on which rows we're looking at:

IF [Table Name] == "Costs + Savings"

THEN [Actual Date]

ELSE DATEADD('month', [Duration in Months], [Actual Date])

END

3. Actual Cost which gives the Cost, but only when we're looking at the cost rows:

IF [Table Name] == "Costs + Savings"

THEN [Cost]

END

4. Actual Savings which gives the Savings, but only when we're looking at the savings rows:

IF [Table Name] == "Costs + Savings1"

THEN [Savings]

END

It may seem like a lot of work, but it pays off!  Because now you have a data set that's relatively easy to use!  And you can create what you want:

Hope that helps!

Joshua

• ###### 2. Re: Two date fields on one combined graph

Thank you Joshua, I will give that a try.  With your solution is it then possible to have Actual Cost and Actual Savings be on the same bar instead of side by side?

• ###### 3. Re: Two date fields on one combined graph

Sure!

Just remove the Measure Names field from Columns and you won't get a separate column for the measures.  This will create a stacked bar.

Best Regards,

Joshua