Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Averages

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

EmployeeEmployee 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     

1 Solution

Accepted Solutions
Not applicable
Author

Hi,

A way to do that is :

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

Benoît

View solution in original post

4 Replies
Not applicable
Author

Hi,

A way to do that is :

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

Benoît

Anonymous
Not applicable
Author

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.

pat_agen
Specialist
Specialist

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

avg.png

Not applicable
Author

Hi,

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