2 Replies Latest reply on May 24, 2016 9:03 PM by barry Moore

    comparing dates to sum average based on results

    barry Moore

      I am a tableau newbie and am having trouble with dates. I want to do something pretty simple but need a little help getting started.


      I want to compare 2 dates (in 2 different columns) and determine if one date is before the other then count it and then show the average as a percentage. Below is an example of the data. Team 1 has 66.6% on time completion rate. 2 early tasks, 1 late task.


      I want to compare completion date to due date and determine if the team achieved the task on time. Then I want to sum them all up to see what percentage of all the records were on time v late.

      Few notes:

      1. Task is 'on time' if completion date <= Due date
      2. Completion date will be null if the task is not complete.
      3. Due date is mandatory
      4. Team is mandatory




      I have attached a spreadsheet and tableau file to this post, but I really haven't gotten very far.


      Appreciate any help folks


      15 May 2016

      TeamDue DateCompletion Date
      Team 130 Apr 201615 Apr 2016
      Team 130 Apr 201606 May 2016
      Team 104 May 201630 Apr 2016

      Team 2

      30 May 2016
        • 1. Re: comparing dates to sum average based on results
          Chris Cantrell

          Hello Barry,

          I set up a new "On time" function, which should give you a little more control of how you would like to deal with the "null" completion date. I also built a completion rate function and dropped it into a crosstab. I think this catches all of your requirements and should at least get you going with the data. Let me know if you have any questions and I will try and answer best I can.

          Have a great Tuesday,




          Edit: I tend to go to numbers rather than phrases, 1 for on time, and 0 for late. Someday, I will be good enough to remember all of the silly details before I hit the reply button. I put incomplete tasks on a filter to switch between pending tasks and what is already completed. Catching tasks which are on the edge of being late for prioritizing might be somewhat messy, but it gets close..

          1 of 1 people found this helpful
          • 2. Re: comparing dates to sum average based on results
            barry Moore

            Thanks Chris. This was exactly what I needed. I also very much appreciate the fast response!