Looking to create a measure that works out how many hours training need to be completed per week until a particular date

(Daniel Glover Silk) #1

I have a looker area that calculates how many hours training staff have completed but as there is a deadline I want to be able to show as a measure how many hours need to be completed per week?

e.g. USERNAME has completed 10:00 hours and has 4 weeks left until the deadline to complete 12 hours of training - so the measure would should show 00:30 to be completed each week.

Thanks in advance.

0 Likes

(Menashe Hamm) #2

Assuming you have a numeric dimensions called total_needed_hours and already_completed_hours, and a dimension of type date_raw called deadline_raw, it would seem to be

dimension: days_until_deadline {
  type: duration_day
  sql_start: current_timestamp ;; # dialect-dependent
  sql_end: ${deadline_raw} ;;
  hidden: yes
}
dimension: weekly_hours_still_needed {
  type: number
  sql:
    (${total_needed_hours}-${already_completed_hours}) * 7 /
    case when ${days_until_deadline}<=0 then null else ${days_until_deadline} end
  ;;
}
1 Like