Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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     

Tags (1)
1 Solution

Accepted Solutions
Not applicable

Averages

Hi,

A way to do that is :

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

Benoît

4 Replies
Not applicable

Averages

Hi,

A way to do that is :

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

Benoît

dennisnet
Valued Contributor III

Averages

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
Valued Contributor

Re: Averages

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

Re: Averages

Hi,

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

Community Browser