Due date versus completed date

[Fro5ty]

I have a series of task dates known as DUE dates and I have recorded the dates the tasks were ACTUALLY completed.

I’m looking to report on how often the tasks were completed on time, early or most importantly late versus the DUE date.

eg. 
Task 1 was completed 3 days early
Task 2 was completed 2 days late
Task 3 was completed on time

As a beginner I’m struggling with this, but I know the answer is out there!

[Dawid]

The first important question is where do you want to do it? LookML/SQL or Custom Dimensions/Table Calculations?

[Fro5ty]

Thank you for replying Dawid - as a beginner, whichever is easiest!
I guess I was expecting to add a calculated field within looker which referenced my dataset fields for due/actual dates and gave me a useable result such as 2days early/ 3 days late which then over a date range I can express as a % of early/on time/late

I hope this makes sense, I basically want to compare two dates to see if something occurred when it should have, then I can focus my attention on improving areas that are not running on time.

[Dawid · Accepted solution]

It would be easier to do it in LookML, especially if you’re going to reuse this boolean field in more than just one query/Look.

In this case you can create a duration dimension group that gives you the difference in days:

dimension: days_after_due_date {
  type: duration_day
  sql_start: ${due_date} ;;
  sql_end: ${actual_date} ;;
}

And then you can also create a boolean field that could be useful to look at distribution of the tasks across those three categories: early, exact, after

dimension: outcome {
  type: string
  sql: CASE
         WHEN ${actual_date} < ${due_date} THEN 'Early'
         WHEN ${actual_date} = ${due_date} THEN 'Exact'
         ELSE 'Late'
       END ;;
}

You can also combine it with a sort field to make sure than regardless of volume, they’re ordered chronologically and not alphabethically:

dimension: outcome_sort {
  hidden: yes
  type: number
  sql: CASE
         WHEN ${actual_date} < ${due_date} THEN 1
         WHEN ${actual_date} = ${due_date} THEN 2
         ELSE 3
       END ;;
}

dimension: outcome {
  type: string
  sql: CASE
         WHEN ${actual_date} < ${due_date} THEN 'Early'
         WHEN ${actual_date} = ${due_date} THEN 'Exact'
         ELSE 'Late'
       END ;;
 order_by_field: outcome_sort
}

[Fro5ty]

Wow - this looks very comprehensive! I just need to take some time to understand it (I have no code experience) and discover where LookML fits into the dashboard builder that I know so far… off to do some reading..

Thank you for taking the time Dawid, it’s much appreciated ?