NoShowAppointments: IDMS_JC where U_IDMS_Type == Appointment, Progress == Lost and CreateDateTime in between now and start of the year before today (01/01/2021).

ConvertedJCs: IDMS_JC where ConvAppointToJcDate not empty, Progress == Won and CreateDateTime in between now and start of the year before today (01/01/2021).

  • CurrentConvertedJCs: from ConvertedJCs above we keep the entries with CreateDate in the selected time frame (YTD, QTD, MTD).
  • PreviousConvertedJCs: from ConvertedJCs above we keep the entries with CreateDate in the time frame (PYTD, PQTD, PMTD).
  • CurrentNoShow: from NoShowAppointments above we keep the entries with CreateDate in the selected time frame (YTD, QTD, MTD).
  • PreviousNoShow: from NoShowAppointments above we keep the entries with CreateDate in the time frame (PYTD, PQTD, PMTD).

Calculations

  • CurrentRate: If CurrentConvertedJCs count > 0 and CurrentNoShow count > 0 then JCLaborsHoursCurrent count / CurrentNoShow count + CurrentConvertedJCs
  • PreviousRate: If PreviousConvertedJCs count > 0 and PreviousNoShow count > 0 then PreviousNoShow count / PreviousNoShow count + PreviousConvertedJCs
  • PercentageChange: If the PreviousRate is > 0 then (CurrentRate – PreviousRate) / PreviousRate.
  • No labels
Write a comment…