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

    Two date fields on one combined graph

    bryan.lubsen.0

      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.

       

            

      ProjectStart YearStart QuarterDuration in MonthsCostSavings
      a20173Q6$10,000.00$3,000.00
      b20174Q10$7,500.00$2,400.00
      c20181Q4$45,000.00$27,000.00
      d20182Q8$18,500.00$6,000.00
      e20193Q2$32,750.00$7,400.00
        • 1. Re: Two date fields on one combined graph
          Joshua Milligan

          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:

           

          DATEADD('month',

          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
            bryan.lubsen.0

            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
              Joshua Milligan

              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