Hi all,
I work in a consultancy firm and for us a key metric is billable utilisation (amount of billable hours in a period / available working hours in a period).
I have three data sets which get connected and transformed through an ETL:
- Input 1. Time report - which includes all hours reported by our consultants (identified by a consultant ID) classified by action codes
- Input 2. Action code lookup - which ties each action code to a time category (PTO, BILLABLE, PROF_DEV, etc.)
- Input 3. Consultants - which contains key details for our consultants (including consultant ID), utilisation % target per month and available working hours per month.
The ETL provides in turn three outputs:
- Output 1. A combined new data set containing all of the inputs linked to each other.
- Output 2. A data set that groups and summarises hours by month, consultant and category, including a column for their available hours per month and their target %.
- Output 3. A data set that filters data set 2 by category="BILLABLE" - I have also done some calculations in the same ETL that calculates utilisation by dividing amount of hours (which are filtered by billable) by the available working hours.
The issue is that for months where a consultant may not have done any billable hour, I I can't get his/her utilisation to work as there is no value for the divisor. Since there would be no hours reported to a code that can be categtorised as BILLABLE, there will be no entries for them at all in Output 3, and no entry under the category "BILLABLE" in Output 3.
I have read the theads below and can't figure out a solution:
https://dojo.domo.com/t5/Beast-Mode-ETL-Dataflow/NULL-Values/m-p/33907#M5291
https://dojo.domo.com/t5/Beast-Mode-ETL-Dataflow/Giving-a-value-to-a-null-field/m-p/19592#M1993
Can anyone help?
Thank you!
Oliver
