3 Replies Latest reply on Oct 3, 2013 4:15 PM by Joe Mako

    Date Range in Line Graphs

    Sameer Mittal

      My sales data looks like this

       

      Product A

       

      Date Quantity
      Sep 10, 201312
      Sep 8, 20135
      Sep 7, 20139
      Sep 6, 201310
      Sep 4, 201314

       

      Product B

       

      Date Quantity
      Sep 8, 20135
      Sep 7, 201311
      Sep 6, 201310
      Sep 5, 20139

       

       

      What I want is to plot the sales data for past 7 days (calculated from the last day of sale) in this case the date range would be Sep 10 to sep 3 for product A and Sep 8 to Sep 1 for product B.

       

      Also I want all the missing dates to show the data point as zero on the line chart any help in this regard is highly appreciated. I tried modifying the data to get me the data in that format but found it difficult.

       

      Thanks.

      Sameer

        • 1. Re: Date Range in Line Graphs
          Ben Neville

          The problem here is that you're asking Tableau not to adjust data to be 0, but to fabricate data and then convert it to zero. The latter, we simply cannot do. If you had a dataset which included Sep. 5th & 9th, but no corresponding sales (and we adjust the nulls to 0's), or with 0 as the sales number, then this would work fine. As is, we cannot create this data.

          • 2. Re: Date Range in Line Graphs
            Matt Lutton

            You may be able to overcome this by creating a "scaffold" data source that includes all the dates.  There is a Video Demo of this concept at the link below.  This has its limitations, as well, though, and it can be difficult to maintain this over time.

            http://community.tableau.com/thread/126855?start=0&tstart=0

             

            I should note that I am NOT an expert on this approach, and have only studied it a bit.  However, this method's championJoe Mako is a very nice guy and is typically more than happy to talk with you on the phone, do a video conference, etc. to help you.  Perhaps he will chime in here to let us know if this is a good use-case for a Scaffold data source.

            1 of 1 people found this helpful
            • 3. Re: Date Range in Line Graphs
              Joe Mako

              How about the attached?

               

              It uses a custom SQL data connection to generate the scaffold data source, and then a data blend to the original data source.

               

              Notice that I draw the marks at 0 two ways, one using the Format Pane options for the continuous pill, and another with a ZN() function in a calculated field (and the ZN() is not in the calc field that brings the value across the blend, to ensure the ZN() happens after blend instead of being sent to the secondary).

               

              You are welcome to reach out to via my email in my profile if you would like more details.

              1 of 1 people found this helpful