Missing value for divisor in formula

Reply
Visitor

Missing value for divisor in formula

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

Tags (1)

Accepted Solutions
Major Red Belt
Major Red Belt

Re: Missing value for divisor in formula

Hi Oliver

 

We have a situation at our company where we have sales goals for products, but sometimes no actual sales are made (per month, or per rep, or per whatever), and we still want to know.  So in a dataflow, I create a matrix of all the dimension possibilities and fill in the gaps with the actual data where it exists.  Then the null or zero values will actually be in the data, instead of just missing entirely.  That makes card interactions and beast modes more usable.

Does that make sense?  Let me know if you need more details.

 

Aaron

 

 

Aaron
MajorDomo @ Merit Medical

**Say "Thanks" by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"

All Replies
Major Red Belt
Major Red Belt

Re: Missing value for divisor in formula

Hi Oliver

 

We have a situation at our company where we have sales goals for products, but sometimes no actual sales are made (per month, or per rep, or per whatever), and we still want to know.  So in a dataflow, I create a matrix of all the dimension possibilities and fill in the gaps with the actual data where it exists.  Then the null or zero values will actually be in the data, instead of just missing entirely.  That makes card interactions and beast modes more usable.

Does that make sense?  Let me know if you need more details.

 

Aaron

 

 

Aaron
MajorDomo @ Merit Medical

**Say "Thanks" by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"
Visitor

Re: Missing value for divisor in formula

Thank you, Aaron! That should do the trick. I'll try to do that and will come back if I need extra help.

 

Thanks!

Announcements
Win free lodging at Domopalooza! We want to hear your Domo story, enter our Dojo contest and win up to four nights lodging at Domopalooza. Click here!