3 Replies Latest reply on Jun 30, 2015 10:05 AM by Rody Zakovich

    Series chart with only start and stop date.

    Michael Franz

      Hello,

       

      I have a customer list that contacts very basic information about them. The Main data is Customer Name, Cust Beg Date, and Cust End Date. What I am looking for is a chart by month that will report the customer count.

       

      ABC Link
      CustomerBeg DateEnd Date
      2/1/20147/1/2015
      Pete Graphics7/1/20128/31/2014
      Champion Mikes1/1/201012/31/2015

       

       

      For example, in the data I have about. The chart would look odd due to the min data

        • 1. Re: Series chart with only start and stop date.
          Rody Zakovich

          Hello Michael,

           

          In order to achieve this, you need to create a Master List of Dates that can be joined on. In Tableau, we can create new "Columns" through the use of calculated fields, duplicated fields, etc, but we cannot create new Records (Rows).

           

          Here is an example using Excel, the concept would be the same if you are using a database, you would just need to create a Table, or a View.

           

          http://kb.tableau.com/articles/knowledgebase/combining-start-end-dates-into-single-axis

           

          I hope this helps.

           

          Regards,

          Rody

          1 of 1 people found this helpful
          • 2. Re: Series chart with only start and stop date.
            Michael Franz

            First, thanks and I got good results at the "month" level but not at the year level and quarter level. One thing I did notice is that this required an extract doe a "countd". I have a concern with the process. In my test sample there were three records, after the join to materdate, I now have 89 rows.

             

            The number of records I am going to be working with will be 2.8M, if this gets compounded at the same ratio, I'll end up with almost 83M records.

             

            Please advise. We are in the design of a data warehouse table so we can add at this time.

             

            I hope I did the attachments correct.

             

            Michael

            • 3. Re: Series chart with only start and stop date.
              Rody Zakovich

              Hello Michael,

               

              In order to report on a granular level, we have to materialize granular data somewhere, which means we are going to have more records and more space.

               

              If the latter is your concern (And the data is simple, like doing a count of customers), I would consider minimizing your Date table to the smallest level of detail you're going to report on. So if you know that you are only going to show a Viz at the level of Month and up, there is no need to materialize every single day that falls between the dates.

               

              You could simply Truncate your Start and End Dates to the Level of Month, and then cross join it (With where clause) against a date table with months.

               

              2015-01-01

              2015-02-01

              2015-03-01

              etc....

               

              Also, instead Joining the tables in Tableau, I would consider creating a View or a physical table in your DB that you can easily connect to in Tableau.

               

              That being said, I am a big believer in having as granular of data as possible. This ensures I have the most flexibility in Tableau to discover insights. Pre-aggregations can lead to miss-informed Dashboards if not properly handled. Space is cheap and Tableau does a good job of handling large datasets, so go granular whenever you can.

               

              I hope this helps.

               

              Let me know if you have any more questions.

               

              Regards,

              Rody