No Data Calc Substitutes Help

[fkli222]

I am working on a utilization report where it calculates the total employee hours recorded / a standard hour base. This works for the most part for individual months but when trying to summarize the quarters is where I run into a problem. For example, if a particular month as no data (hours), it excludes the base of the month with no data. If each of the months standard hours is 175, it would be your total hours over 525. If 2 of the months have no data, it only shows total hours over 175 instead of 525.

My Standard Hours Total formula used for quarters is: Total (distinct [Standard Hours] for [Quarter], [Accounting Period])

Is there another way to lay this out so if a month is blank it will still apply 525 to a quarter? Also see below for screenshot of the columns. There is also rows for the employees but not being shown.

[bus_pass_man]

If I understand correctly, the rows represent employees and the report is a comparison of the utilization of the employees versus a standard hour metric.  One employee, let's call him smegi, has nulls for two months in a quarter.

The first question I'd need to ask is, how do you know that the two nulls are a problem?

You'd need to understand the business situation and whether, if the value is null, should it be null or should it be zero?  There's a significant difference.

When you say 'has no data' do mean

query item                value
employee hours recorded   null
standard hour base       null
or
employee hours recorded   null
standard hour base       175
or
employee hours recorded   { number}
standard hour base       null


What was smegi doing in those 2 months?   Was he actually working in this function?  If not, then having nulls would make perfect sense.   Another way to think through the problem would be if the first month had values and the second and third months did not.   Would that mean that smegi was no longer doing {whatever the job he was doing}?  For example, he could have quit, been sacked, or moved to another position.

Again, you'd need to need to understand the business situation.

If you really need to those two cells to not be null then you might want to have some handling for the nulls.  For example, coalesce ( employee hours recorded, 0) or coalesce (standard hour base , 175) (although it might be better if the number was captured in a calculation or query item and that object used as a reference in stead of the number)

hope that helps