Qlik Community

Ask a Question

Qlik Sense App Development

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

Announcements
Do More with Qlik - for Beginners and Beyond, Topic: Qlik Replicate on January 21, 2PM EST. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Creator II
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 NoEmployee NameWage CategoryHours Worked
1AndyOrdinary Hours50
1AndyAnnual Leave10
1AndySick Leave5
2DavidOrdinary Hours60
2DavidAnnual Leave15
2DavidSick Leave5
3MaryOrdinary Hours55
3MaryAnnual Leave1
3MarySick Leave2
4SueOrdinary Hours52
4SueAnnual Leave12
4SueSick Leave3

 

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 NoContracted Hours
170
270
350
472

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 NoEmployee NameWage CategoryHours WorkedContracted Hours
1AndyOrdinary Hours5070
1AndyAnnual Leave1070
1AndySick Leave570
2DavidOrdinary Hours6070
2DavidAnnual Leave1570
2DavidSick Leave570
3MaryOrdinary Hours5550
3MaryAnnual Leave150
3MarySick Leave250
4SueOrdinary Hours5272
4SueAnnual Leave1272
4SueSick Leave372

 

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 LabelsContracted HoursHours Worked
Andy7065
David7080
Mary5058
Sue7267
Grand Total65.5270

 

Thank you in advance.

Sorry for the length of the question.

Kind regards,

Andy

1 Solution

Accepted Solutions
Partner
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])

LOAD 

[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

View solution in original post

2 Replies
Partner
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])

LOAD 

[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

View solution in original post

Creator II
Creator II

Hi Sergey,

Thanks a lot for your reply.

Yes, the Concatenate function does the job instead of using the Join function.

Great.

Kind regards,

Andrew