Averages in a graph

[#1]

Hi

I am hoping someone can help...

I am trying to calculate Utilisation %'s. I currently have a table, using dimensions: Company, Centre, Month, Week, Employee. Off the back of this, I have a breakdown of hours (Presence, Sick, Project Hours) as my expressions

I also have an expression in this table to calculate a utilisation %:

sum(if(BOOKING_TYPE=Direct,HOURS))

/ (sum(distinct if(Date,PRESENT_HOURS))

+ sum(distant if(Date,OVERTIME_HOURS)))

e.g.

Company     Centre       Month      Week    Employee    Presence   Overtime      Direct       Utilisation %

                                                                                      66.5         56.75       147.25            79.06%

13                  769          May          18        123456           29.5         25.5           30.5             55.45%

13                  769          May          19        123456           29.5         17.5           38                80.85%

13                  769          May          20        123456           37            3               37                92.50%

13                  769          May          21        123456           37            10.75        41.75            87.43%

Against the Utilisation expression, I have the Total set to 'Avg of rows' to provide the 79.06% figure

What I am now trying to do, is to provide a line graph, tracking the utilisation % per week/month. I want to be able to do this at Centre level, but if I did select an Employee, then I would expect to see their Utilisation %'s over the selected period

For this, I have used the dimensions: Date and Centre, whilst using the same expression calculation as above. Based on the results, the average calculation for May becomes 119.47% instead of 79.06%

I can replicate this figure within the table (I am using the expression default as the Total option). In the graph itself, I have got the Average Total of rows selected, but it still shows 119.47%

Any help would be appreciated

Thanks

Ben

[danielrozental]

Can you post an example application?

Or at least post the expression you're using.

[#3]

Hi,

The expression I am using is:

sum(if(BOOKING_TYPE=Direct,HOURS))

/ (sum(distinct if(Date,PRESENT_HOURS))

+ sum(distant if(Date,OVERTIME_HOURS)))

[hdonald]

Hi,

It could be that the 'distinct' in the expression is causing a problem - the 'Presence' total of 66.5 seems too low because it's only adding the distinct values of 29.5 and 37, rather than double that.  This would generate the 119.47% figure,

Regards,

HD

[#5]

Hi HD,

Thanks for the reply

The individual calculation for presence hours now works (sum the total, instead of the expression total)

The reason I need a distinct reference is because I have joined two separate tables together. One includes Project hours, and the other the attendance. The problem I have, is that all Project rows need to have their hours counted. However, the attendance values should only be calculated once per the date (otherwise I end up with more attendance hours)

In theory, the calculation should be:

Direct Hours / (Presence + Overtime) - in this case: 147.25 / (133+56.75)*100 = 77.60%

Any idea?

Thanks so far!

[hdonald]

Tricky to say without seeing the context, but you could try using the Aggr(expression,dimensions) syntax to mimic the behaviour of the pivot table in the chart,

e.g. force the expression to calculate each of the three parts of the sum per week/employee combination (which I'm assuming are not included in the chart)

perhaps like ,

sum( Aggr( if(BOOKING_TYPE=Direct,HOURS), Week, Employee)  )

/  etc..

but it may be better to post an example .qvw as a data modelling problem and try and simplify the underlying tables first (other contributors will have had the same modelling issues, no doubt),

Regards,

HD

[#7]

Thanks again for the reply

I had a quick go at the aggr function, but couldn't get any output values

I attach a test qvw which mimics the production version I am using

If I select a week (which includes both employees) i get a utilisation % in the table, but the percentage in the graph does not match

Many Thanks

Ben

[hdonald · Accepted solution]

Hi,

For the average 'Utilization %'  try working out each % rate per week and employee, then taking an average of that - as in the following,

=  avg(  aggr(

sum(if([Booking Type]='Direct Hours',Hours))/

(sum(distinct if(Week,[Presence Hours]))

+ sum(distinct if(Week,[Overtime Hours])))

, Week, Employee ))

Regards,

HD

[#9]

Thanks HD - that works just as expected!

Thanks for the support!