Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

msmcilvride
New Contributor II

Average of certain values in row of pivot table

I have a pivot table that looks something like this:

Employee NameMonday
Clock In TimeClock Out TimeTime Worked
Arnold9:00 AM5:00 PM08:00

It includes data for weekdays Monday - Friday. The data tab for my pivot table looks like this:

Rows

EmployeeName

Columns

DayOfWeek

Measures

ClockInTime

ClockOutTime

TimeWorked

I would like for my pivot table to include a weekly average of time worked based on each day's TimeWorked value. How could I go about this?

10 Replies
MVP
MVP

Re: Average of certain values in row of pivot table

What is your expression for TimeWorked? and where would you like to see the Average in your chart?

msmcilvride
New Contributor II

Re: Average of certain values in row of pivot table

My expression for TimeWorked is Interval( ClockOutTime  - ClockInTime, 'hh:mm') and it would be great to have the average at the end of the row.

MVP
MVP

Re: Average of certain values in row of pivot table

May be this

Interval(Avg(ClockOutTime  - ClockInTime), 'hh:mm')

and enable the totals for Employee Name dimension

msmcilvride
New Contributor II

Re: Average of certain values in row of pivot table

I have tried that expression, but it does the average for each day, which is the same value as Time Worked. I would like for it to average the entire week, but since Day is being used as the column in the pivot table I am having a hard time doing this.

I did look for totals, but I cannot find them anywhere in my pivot table settings (does not appear in Data, Sorting, Add-ons, or Appearance).

Edit:

I see now that I can include totals. However, it totals the columns and not the rows.

MVP
MVP

Re: Average of certain values in row of pivot table

Have you enable the total for the Employee Name field?

msmcilvride
New Contributor II

Re: Average of certain values in row of pivot table

I did. However, the totals are by column, not by row. Is there a way to total them by row?

MVP
MVP

Re: Average of certain values in row of pivot table

Enable the total for your Day/Date dimension

msmcilvride
New Contributor II

Re: Average of certain values in row of pivot table

That is much better! Is there a way to specify which values to total?

For instance, right now it shows me the first time an employee clocked in for the week and the last time, which I don't need to see. I would also like to see an average for the week, but not per day (as per day average is just time worked anyway).

MVP
MVP

Re: Average of certain values in row of pivot table

You can configure this using an approach which have been used many times on the community... sharing some of those to help you understand it better

Qlikview Pivot Chart with new dimension and expression

Percentage Subtotal

Percent of Total in Pivot Table

Pivot problem for adding another dimension | Qlik Community

Community Browser