topic Re: average in straight table in QlikView

average in straight table

alexpanjhc — Tue, 02 Apr 2013 22:15:27 GMT

I need to calculate the average utilization in the attached picture.

I need to get an average rate for the entire group by using the billed hours total/ standard hours total.(they are both marked red in the pic)

and the rate should show in average utilization.

How do I achieve that?

Thanks!

Re: average in straight table

Wed, 03 Apr 2013 04:59:55 GMT

Sum(TOTAL billed_hours)/sum(TOTAL standard_hours)

should help.

Regards,

Kiran Rokkam

Re: average in straight table

jagan — Wed, 03 Apr 2013 06:20:05 GMT

Hi,

You can get the total value of a dimension by using the TOTAL keyword in the aggregate function. By using TOTAL it ignores the dimensions in the chart.

=Sum(TOTAL billedHours) will give you the total billed hours.

=sum(TOTAL standardHours) will give you the total standard hours.

Hope this helps you.

Regards,

Jagan.

Re: average in straight table

alexpanjhc — Wed, 03 Apr 2013 13:29:14 GMT

Both billed hours and standard hours are calculated fields. billed hours used a set analysis and standarad hour was a variable.

billed:

Sum

({<Module={'FEE'}, bill_status={'B'} ,[Trans Date]= {'>=$(=vStart)<$(=Addmonths( date(vStart), num(Interval)))'}>} Hours)

Standard:

(Column(1)=0,0, if ( date(SelectStart)<date(EffectDate) and   (AddMonths(vStart , Interval) -1)<EffectDate , 0,
if ( date(SelectStart)<date(EffectDate) and   (AddMonths(vStart , Interval) -1)>EndDate , 173.3* ((EndDate-EffectDate)/30) ,
if ( date(SelectStart)<date(EffectDate) and   (AddMonths(vStart , Interval) -1)<= EndDate , 173.3*(((AddMonths(vStart , Interval) -1)-EffectDate)/30) ,
if ( date(SelectStart)>=date(EffectDate) and   (AddMonths(vStart , Interval) -1)<= EndDate   , 173.3* ((AddMonths(vStart , Interval) -1)-SelectStart )/30 ,
if ( date(SelectStart)>=date(EffectDate) and   (AddMonths(vStart , Interval) -1)> EndDate   , 173.3* (EndDate-SelectStart )/30 ,
if ( date(SelectStart)>=date(EffectDate) and   date(SelectStart)> EndDate   , 0
)
)
)
)
))

)

so i tried what you guys suggested

sum(total

sum({<Module={'FEE'}, bill_status={'B'} ,[Trans Date]= {'>=$(=vStart)<$(=Addmonths( date(vStart), num(Interval)))'}>} Hours) )/

sum( total $(vStd1))-> this is the variable so that I do not copy a lot of codes.

this is not working, it showed the dash. I then replaced the variable using the actual set analysis. I was unable to get the correct answer either.

where was I wrong?