# Qlik Sense App Development

Announcements
Do More with Qlik - for Beginners and Beyond, Topic: Qlik Replicate on January 21, 2PM EST. REGISTER NOW
cancel
Showing results for
Did you mean:
Creator II

## Calculate the sum of average values in Pivot Table

Hi,

Got a question and need to get my head around how to fix it.

I got some employee data set looks like below (Table 1).

 Employee No Employee Name Wage Category Hours Worked 1 Andy Ordinary Hours 50 1 Andy Annual Leave 10 1 Andy Sick Leave 5 2 David Ordinary Hours 60 2 David Annual Leave 15 2 David Sick Leave 5 3 Mary Ordinary Hours 55 3 Mary Annual Leave 1 3 Mary Sick Leave 2 4 Sue Ordinary Hours 52 4 Sue Annual Leave 12 4 Sue Sick Leave 3

The above table demonstrates the actual hour of wage category data for 4 employees for a fortnight.

In addition to above, I got another table which looks like below and it got data for employee contracted hours for a fortnight (Table 2).

 Employee No Contracted Hours 1 70 2 70 3 50 4 72

Requirement;

I want to compare the total wage category hours against the contracted hours for each employee. Thus, during the data loading process, I used "Left Join" command to add the contracted hours of data to the Table 1 data.

Now the final data set looks like below;

 Employee No Employee Name Wage Category Hours Worked Contracted Hours 1 Andy Ordinary Hours 50 70 1 Andy Annual Leave 10 70 1 Andy Sick Leave 5 70 2 David Ordinary Hours 60 70 2 David Annual Leave 15 70 2 David Sick Leave 5 70 3 Mary Ordinary Hours 55 50 3 Mary Annual Leave 1 50 3 Mary Sick Leave 2 50 4 Sue Ordinary Hours 52 72 4 Sue Annual Leave 12 72 4 Sue Sick Leave 3 72

In the pivot table, I created a measure named "Contracted Hours" and the calculated expression is "Avg([Contracted Hours])"  and for each Wage Category item a separate measure of:

Sum(If([Wage Category ]='Ordinary Hours',[Hours Worked]))

Sum(If([Wage Category ]='Annual Leave',[Hours Worked]))

Sum(If([Wage Category ]='Sick Leave',[Hours Worked]))

Question;

I want to show the "Total" function of the pivot table and if I use it, all the Wage Categories will show the correct total value for each Wage Category except for the " Contracted Hours" because my calculation expression is "Average".

Appreciate your feedback on a method to overcome this issue in my data processing model.

The pivot table I have done in Excel and will looks like below;

 Row Labels Contracted Hours Hours Worked Andy 70 65 David 70 80 Mary 50 58 Sue 72 67 Grand Total 65.5 270

Sorry for the length of the question.

Kind regards,

Andy

1 Solution

Accepted Solutions
Partner

Hi Andy,

I think you need to concatenate Contracted Hours, but not join them.

Although you  need to prepare data first.

//0. I assume you have loaded Table 1 in memory.

//1. Create a map with Employees

MAP_EmpID2Employee:

MAPPING LOAD [Employee No], [Employee Name]

Resident [Table 1];

//2. Prepare you data when you load Table 2 and concatenate it with table 1

Concatenate([Table 1])

[Employee No],

ApplyMap('MAP_EmpID2Employee', [Employee No],'No match in Table 1 for ' & [Employee No]) AS [Employee Name],

'Contracted Hours' AS [Wage Category],

[Contracted Hours] AS [Hours Worked]

FROM YOUR_DATA_SOURCE;

Then you can use Expression

For Contracted SUM ({\$<[Wage Category]={'Contracted Hours' }>}[Hours Worked]

For Hours Worked SUM ({\$<[Wage Category]-={'Contracted Hours' }>}[Hours Worked]

Hope it helps

Regards,
Sergey
2 Replies
Partner

Hi Andy,

I think you need to concatenate Contracted Hours, but not join them.

Although you  need to prepare data first.

//0. I assume you have loaded Table 1 in memory.

//1. Create a map with Employees

MAP_EmpID2Employee:

MAPPING LOAD [Employee No], [Employee Name]

Resident [Table 1];

//2. Prepare you data when you load Table 2 and concatenate it with table 1

Concatenate([Table 1])

[Employee No],

ApplyMap('MAP_EmpID2Employee', [Employee No],'No match in Table 1 for ' & [Employee No]) AS [Employee Name],

'Contracted Hours' AS [Wage Category],

[Contracted Hours] AS [Hours Worked]

FROM YOUR_DATA_SOURCE;

Then you can use Expression

For Contracted SUM ({\$<[Wage Category]={'Contracted Hours' }>}[Hours Worked]

For Hours Worked SUM ({\$<[Wage Category]-={'Contracted Hours' }>}[Hours Worked]

Hope it helps

Regards,
Sergey
Creator II

Hi Sergey,