need help with straight table calculation

[alexpanjhc]

Hi

I need to calculate the average utilization rate for each level.

I calculated the worked hours, and standard hours, However, in the standard hours I calculated if worked hours is 0, then the standard hour is zero too,

work and standard hours are Ok so is utilization rate.

The problem is the Average utilization

here is my expression under average utilzation for level:

sum(total <level> worked_hours)

/sum( total <level> if (column(1)=0,0, std_hrs))

standar hour is:(the reason I use column(1) is because the worked hour has a big calculation, I do not want to bring in)

if (column(1)=0,0, std_hrs)

so I am expecting to get the correct average rate, but it is not correct. It is still adding up standard hours for those whose working hours are zero.

Maybe someone can tell me what I am missing?

PS, I do not want to suppress these employees in the chart. I updated  with my qvw.

Thanks for helping!

[swuehl]

I don't think you can reference column(1) like this in your context.

I assume (and it would make things much easier if you could post a small sample or describe your chart settings a bit more complete (how many / what dimensions and definition of your expressions) ) you are using one dimension level, so try something like

sum(total<level> worked_hours)

/ sum({<level = {"=sum(worked_hours)>0"}>} total<level> std_hrs)

[alexpanjhc]

Thanks for looking at his!

This is a bigger picture of what I am doing.

Let me think of how to get a smaller sample to get a qvw.But here are some of the key components

Dimensions:

Level, Employee, Hire Date

worked hours:

Sum

({<Module={'FEE'}, [Client Type] ={'Billable','Other Billing'},  [Trans Date]= {'>=$(=vStart)<$(=Addmonths( date(vStart), num(Interval)))'}>} W_Hours)

Standard hours:

if

(column(1)=0,0,sum({< StartSelection= {'>=$(=vStart)<$(=Addmonths( date(vStart), num(Interval)))'}>}STD_Hrs))

Utilzation:

Sum

({<Module={'FEE'}, [Client Type] ={'Billable','Other Billing'},  [Trans Date]= {'>=$(=vStart)<$(=Addmonths( date(vStart), num(Interval)))'}>} W_Hours

if

(column(1)=0,0,sum({< StartSelection= {'>=$(=vStart)<$(=Addmonths( date(vStart), num(Interval)))'}>}STD_Hrs))

)
/

[alexpanjhc]

Hi swuehl

I updated with qvw.

Thanks for looking.