3 Replies Latest reply on Sep 6, 2016 10:16 PM by Mahfooj Khan

    Need clarity on creating a trend graph & merging multiple trend graphs.

    Maheshh C K

      Dear Team,

       

      I have a requirement in creation of trend graph.

       

      Sample Input:

          

      Event NameEvent StatusEvent Start DateEvent Closed DateEvent Cancelled Date
      E1Open1/1/2016
      E2Open2/1/2016
      E3Open3/1/2016
      E4Open4/1/2016
      E5Closed5/1/20166/1/2016
      E6Closed6/1/20167/1/2016
      E7Cancelled7/1/2016 7/31/2016
      E8Open8/1/2016
      E9Open9/1/2016
      E10Open10/1/2016
      E11Cancelled11/1/2016 12/1/2016
      E12Cancelled12/1/2016 12/1/2016
      E13Closed1/1/20171/1/2017
      E14Closed2/1/20173/1/2017
      E15Closed3/1/20174/1/2017
      E16Open4/1/2017
      E17Open5/1/2017
      E18Open6/1/2017
      E19Open7/1/2017
      E20Open8/1/2017

       

      Expected Outcome:

      The following should be showcased as 3 trend lines.

      1) Number of Events by Event Start Date Quarter.

      2) Number of Events by Outgoing Event Date Quarter. (Outgoing Event Date= Event Closed Date when status is closed & Event Cancelled Date when event status is Cancelled)

      3) Cumulative sum of open events across the time frame.

       

       

      Tableau stuck points:

      I tried generating the above in Tableau & got stuck in the following areas.

       

      Point#1: Blue Line Indicator

      No problem

       

      Point#2: Orange line indicator

      1) The 2016 Q1 & 2017 Q3 – I was unable to draw lines for 0.

      2) I got stuck up with the line color coding as the lines are represented only in blue for both Point#1 & Point#2

       

      Point#3: Gray line indicator

      1) 2017 Q1 – shows 7 instead of 8 (Not sure if am doing something wrong here in tableau)

      2) There is one unwanted trend line of constant 12 gray line (Not sure how to remove this)

      3) Am unable to merge all 3 graphs together.

       

      I have attached the .twbx file for the reference.

      Am a newbee in Tableau. Kindly help me on the same.

       

       

      Thanks

      Maheshh

        • 1. Re: Need clarity on creating a trend graph & merging multiple trend graphs.
          Mahfooj Khan

          Is it possible to modify your data structure? If there is any provision then it'll be very easy for you to get the desired output. You have to transpose you Event Dates into rows for each event. After transpose it should look like.

          Sample workbook has been attached for your reference. Have a look and let me know If you've any query.

           

          Mahfooj

          • 2. Re: Need clarity on creating a trend graph & merging multiple trend graphs.
            Maheshh C K

            Dear Mahfooj/Team,

             

            Thanks for your response.

            On first note, I would keep changing the data structure to be the last choice.

             

            Moreover, I did see the workbook. But my requirement/clarification is different from the one that you have shared.

             

            My requirement for point#2 is to

            Number of Events by Outgoing Event Date Quarter. i.e. (Outgoing Event Date= Event Closed Date when status is closed & Event Cancelled Date when event status is Cancelled)

            I have created a calculated field for the same & plotted the line graph in my attached workbook. However I was unable to change the color for the same to distinguish between the point#1. Kindly let me know if this can be done.

             

            Point#3:

            This is bit tricky. The below will depict more on the calculation for the input given.

            For 2016 Q1

                 Cumulative Open Events = Number of Events Open - Number of Events Closed/Cancelled for 2016 Q1

                                                        = 3-0=3

            For 2016 Q2

                 Cumulative Open Events = (Number of Events Open - Number of Events Closed/Cancelled for 2016 Q2) + Cumulative Open Events for 2016 Q1

                                                        = (3-1)+3=5

            For 2016 Q3

                 Cumulative Open Events = (Number of Events Open - Number of Events Closed/Cancelled for 2016 Q3) + Cumulative Open Events for 2016 Q2

                                                         =(3-2)+5=6

             

            & is is calculated so on..

             

            For the given input, I have manually calculated the cumulative Open Events as below.

                

            Number of Events OpenNumber of Events Closed/CancelledCumulative Open Events
            2016 Q1303
            2016 Q2315
            2016 Q3326
            2016 Q4327
            2017 Q1328
            2017 Q23110
            2017 Q32012

             

            I have created the chart in excel for the same it looks to be.

             

             

            Kindly help me doing it in Tableau.

             

            Thanks

            Maheshh

            • 3. Re: Need clarity on creating a trend graph & merging multiple trend graphs.
              Mahfooj Khan

              This is what I'm getting right now. Will it be okay?