topic Averages in a graph in QlikView

Averages in a graph

Mon, 18 Jul 2011 15:48:22 GMT

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

Averages in a graph

danielrozental — Mon, 18 Jul 2011 16:50:25 GMT

Can you post an example application?

Or at least post the expression you're using.

Averages in a graph

Tue, 19 Jul 2011 06:06:05 GMT

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)))

Averages in a graph

hdonald — Tue, 19 Jul 2011 08:47:53 GMT

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,

Averages in a graph

Tue, 19 Jul 2011 09:23:42 GMT

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!

Averages in a graph

hdonald — Tue, 19 Jul 2011 10:47:23 GMT

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,

Re: Averages in a graph

Wed, 20 Jul 2011 09:52:20 GMT

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

Re: Averages in a graph

hdonald — Wed, 20 Jul 2011 15:58:57 GMT

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,

Re: Averages in a graph

Thu, 21 Jul 2011 06:18:34 GMT

Thanks HD - that works just as expected!

Thanks for the support!