1 Reply Latest reply on May 11, 2017 12:35 PM by Walt Reed

    Number of dates in range

    George Rooney
    George Rooney May 11, 2017 12:18 PM

      Hi all,

       

      I'm looking to produce a couple of charts here.  My data are just a series of start and end dates with a key.  Sometimes there is no end date, but I don't think that should be a huge issue.

       

      The first would have a chart with time (weekly) on the x-axis and two measures which count the numbers started this week, and number ending this week.  I'm able to get #  started this week, and # started this week that have been closed, but that doesn't reflect when they ended.

       

      The second would be the number of ongoing items, ones that have begun but not ended yet.  It would just be a running sum of the difference of the first two measures.

       

      Thanks,

      George

      • 74 Views
      • Categories: Calculations, Dates & Times
      • Tags:
        • 1. Re: Number of dates in range
          Walt Reed
          Walt Reed May 11, 2017 12:35 PM (in response to George Rooney)

          Hi George,

          For your first measure (Number started this week), simply drag Number of Records onto the viz.

          For the second measure (Number ending this week), you want to compare the week number for the Start Time to week number for Close Time, and if they are the same, assign a value of 1. Drag this onto the viz and aggregate by COUNT.

          For the third measure (Ongoing Items), you just want to count which items have a NULL Close Time. Right-click drag this onto the viz, and aggregate by COUNT:

          It looks like I have a newer version of Tableau than you so I can't share my workbook, but here's a screenshot of the final result. Let me know if this isn't what you're looking for!

           

          Walt

          2 of 2 people found this helpful