topic Re: Averages in QlikView

Averages

Thu, 12 May 2011 06:38:56 GMT

Hi,

I have a Pivot table used to display the number of hours an Employee works over a number of weeks. This is in the format of:

Week | 1 2 3 4 5 ...

Employee | Employee Name 10 20 10 10 10

The dimensions are: Employee ID, Name, Week_no

The expression is a sum of hours that count towards actual working hours: sum(total_hours_attended)

What I would like to be able to do, is to select a number of weeks (in this case 5) and at the end of the row, provide an overall average, e.g:

Week | 1 2 3 4 5 Average

Employee | Employee Name 10 20 10 10 10 12

If I create another expression to provide an average, it will average each week. Is there a way to have the average calculate at the end of the line?

Thanks in advance

Ben

Averages

Thu, 12 May 2011 07:41:15 GMT

Hi,

A way to do that is :

if( columnNo() = 0 , sum(total_hours_attended)/count(distinct week) , sum(total_hours_attended) )

Benoît

Averages

Anonymous — Thu, 12 May 2011 07:47:13 GMT

Hi ben,

I know one way to do this , but only if the hours in your expression is not a Sum().

If your expression is just [Hours] you can change this to AVG[Hours] , this will return the average on every line but this will be the same if you don't use a calculation in your expression (fe: AVG(10) = 10)

So this way you have the right hours in your expresion.

Now go to the tab Presentation and now check "Show Partial Sums" for Employee Name.

Hope this works for you.

Re: Averages

pat_agen — Thu, 12 May 2011 08:06:48 GMT

hi,

if you use as expression

sum(hours)/count(distinct Week)

and on the presentation tab you tick "show partial sums" for Week and change the "Label for Totals" to "avg" for the same

you will get the following

Re: Averages

Thu, 12 May 2011 10:29:25 GMT

Hi,

I have used Bcuisiniere's code and implemented what you wanted.