topic Re: need help with straight table calculation in QlikView

need help with straight table calculation

alexpanjhc — Wed, 01 May 2013 22:19:57 GMT

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!

Re: need help with straight table calculation

swuehl — Wed, 01 May 2013 22:44:56 GMT

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)

Re: need help with straight table calculation

alexpanjhc — Wed, 01 May 2013 23:01:26 GMT

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:

(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

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

)
/

Re: need help with straight table calculation

alexpanjhc — Thu, 02 May 2013 14:03:07 GMT

Hi swuehl

I updated with qvw.

Thanks for looking.