6 Replies Latest reply on Apr 17, 2017 12:41 AM by Pankhudi Gupta

    To display multiple date fields (columns) in the single line chart

    Pankhudi Gupta

      I have a sales scenario here. One lead will cross multiple stages to reach a complete sales process,

      along with its dates like lead start date -> contract sign date -> training begin date -> training end date -> Order placing date -> sale closed day.

      for each lead I have all these date columns.

      Aim: To identify how fast (in days) each lead goes from 1 phase to other.

       

      For example: For this sample source:

         

      leadPhase 1 date

      Phase 2

      date

      Phase 3

      date

      Phase 4

      date

      ab3-Jan-177-Jan-1710-Jan-1730-Jan-17
      cd4-Jan-179-Jan-1714-Jan-1719-Jan-17
      ef5-Feb-1711-Feb-1717-Feb-1723-Feb-17
      gh5-Mar-1712-Mar-1719-Mar-1726-Mar-17

       

      This could be a representation: (Not the best one though as it would cause problem in case the leads are in thousands)

       

      I am attaching a sample data source to be able to connect with tableau.

      Found this quite challenging! Someone kindly help!

       

        • 1. Re: To display multiple date fields (columns) in the single line chart
          Mahfooj Khan

          Hi Pankhudi,

           

          Few quick question regarding your data source.

          1. Could you please tell us is it one time activity or long term activity to create this report?

          2. Your data source is excel only or any DB?

          3. Is there any provision to change the format of the data source?

           

          After seeing your data I would suggest you need the format the structure of your data source first. Transpose the Date fields so that all the date columns comes in a single field with its description. Like this

          If this is one time activity then you can connect your original source in tableau then select all the date fields and Pivot like this

          Once done then you'll get Pivoted field names and Values like this. I've renamed the Pivot fields.

          Once done then just go to sheet and play with the data. Drag the fields in canvas.

          Above solution works well if its once time activity. Else reconstruct the structure of your source at database level. If your source is excel only then transpose columns then connect to source tableau.

           

          If you can't change the format and your data source is excel only then connect your source with legacy connection there you'll have option to write Custom SQL query that will help you to transpose the data.

           

          Let us know if you've any question.

           

          Mahfooj

          1 of 1 people found this helpful
          • 2. Re: To display multiple date fields (columns) in the single line chart
            Matthew Risley

            What should the order of the phases be? They are different in the data source that you gave us.

            • 3. Re: To display multiple date fields (columns) in the single line chart
              Pankhudi Gupta

              Hi Mahfooj,

              Thanks for you descriptive solution, but I had considered this solution before, and realized that this approach will not be a suitable solution for my problem.

              To answer your queries,

              Currently I am using excel to design this report, but for deployment it will be connected to a oracle database, and its creation will be a one time activity.

              The report would regularly get refreshed as the new data comes in the database.

               

              Our data is going to be very huge and though the phases will be fixed to 6 or 7 phases, the leads will be in thousands initially.

              Hence, according to your solution, the rows would become 1000*(6/7) times.

              This would greatly impact the report performance, moreover require a lot of extra memory.

              This is a major threat after deployment as this report will be viewed by many people on the server regularly and it should not take more loading time than a minute.

               

              Is it possible to use some other representation to display these KPI's effectively, without modifying the data source structure?

              Kindly suggest.

              • 4. Re: To display multiple date fields (columns) in the single line chart
                Pankhudi Gupta

                Hi Matthew,

                The phase fields in the excel are in the correct format.

                If you look at a sales cycle generally, it begins by lead generation and ends with revenue recognition (when the lead places its first order).

                 

                I will suggest here our focus should be trying to imagine the right way to display the transition of each lead from one phase to the other.

                Hence being able to identify that on an average which stage of the sales cycle is working slowly and be able to make it faster.

                 

                We can still modify the phase later as we want. Also, for now, the stage field is redundant.

                Kindly suggest if you have any creative ideas!

                 

                Regards,

                Pankhudi

                • 5. Re: To display multiple date fields (columns) in the single line chart
                  Mahfooj Khan

                  Hi Pankhudi,

                   

                  If you've read the last para where I've mentioned if you don't want to change the format then use Custom sql to transpose the data source. In custom sql you can use union to transpose/pivot your data. E.g

                   

                  Select Lead, Phase1 AS Date, "Phase1" AS Phase from Table

                  union

                  Select Lead, Phase2, "Phase2" from Table

                  union

                  Select Lead, Phase3, "Phase3" from Table

                  ....

                  ....

                   

                  I've read somewhere tableau works well in depth (rows) of the data source rather than width (columns). Performance degrade when you've more columns. It works well in millions not in billion records.

                   

                  Mahfooj

                  2 of 2 people found this helpful
                  • 6. Re: To display multiple date fields (columns) in the single line chart
                    Pankhudi Gupta

                    Alright Let me try that, and see how that affects the performance.

                    My issue here would still be representation.

                     

                    The graph would not let us drag lead name to colors once the lead number increases more than 30 or so, as no palette would provide 30 or more unique colors.

                    Also the graph would look very crowded with hundreds of multi color lines of leads. If we need to sort by top 10 leads with the shortest phase duration for any phase its still a challenge.

                    Maybe you can try using the data source I have provided to come up with a better representation.

                     

                    Thanks!

                    Pankhudi.