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

Totals expression calculation issue

Hi,

This question is an expansion of the issues encountered in the thread http://community.qlik.com/message/189565#189565

This time, I added a column called "Cost" and I used the expression Column(2) * Column(3) ("Total Employee Hours" * "Employee Hourly Cost"). The individual costs for  EMP1, EMP3, EMP4, EMP7 for the selected activity "Cu A4" are calculated correctly. The total though is 417,93 but the sum of the individuals is 341,59 and I cannot figure out why this happens.

1 Solution

Accepted Solutions
swuehl
MVP
MVP

Try this as expression for Cost:

sum(aggr(

sum(HoursWorked) *

sum({<EmpName={"=sum(HoursWorked)>0"}>}  EmpTotalCost)

/ sum({<Activities= , EmpId={"=sum(HoursWorked)>0"}>} total <EmpName> HoursWorked)

,EmpName))

This will get you the expected sum of rows (or try using a straight table with total mode "sum of rows").

The reason why you don't get the correct, expected total (well, it is correct, since the total is indeed Column(2)*column(3) ) is that you calculated your hourly costs based on total costs and the number of hours a Emp spent on ALL activities. If he spends a proportional larger or smaller amount of time on the activity you are currently selecting, you would need to use a weight to correct for this, or just use a sum of rows.

Not sure if I make my self clear.

I also think it would make some things easier, if you e.g. calculate the hourly cost per employee in the script, since it seems to be static per employee.

View solution in original post

7 Replies
swuehl
MVP
MVP

Try this as expression for Cost:

sum(aggr(

sum(HoursWorked) *

sum({<EmpName={"=sum(HoursWorked)>0"}>}  EmpTotalCost)

/ sum({<Activities= , EmpId={"=sum(HoursWorked)>0"}>} total <EmpName> HoursWorked)

,EmpName))

This will get you the expected sum of rows (or try using a straight table with total mode "sum of rows").

The reason why you don't get the correct, expected total (well, it is correct, since the total is indeed Column(2)*column(3) ) is that you calculated your hourly costs based on total costs and the number of hours a Emp spent on ALL activities. If he spends a proportional larger or smaller amount of time on the activity you are currently selecting, you would need to use a weight to correct for this, or just use a sum of rows.

Not sure if I make my self clear.

I also think it would make some things easier, if you e.g. calculate the hourly cost per employee in the script, since it seems to be static per employee.

Not applicable
Author

Hi...

I really need to use a pivot table.

Your answer is correct based on the information I provided you and therefore I mark it as correct. In the example I gave you, I omitted - because I thought it wouldn't matter - to add the dimension of "Activities" and that would be the columns, with the Employees being the rows. In that case, your formula works fine as long as I have made a selection of an activity. The intention though, is to have all activities and employees shown until the user makes a selection. In this case, with all activities, the column "Cost" that uses the formula you gave me, is not working correctly. If you want you can help on this problem expansion or I can post another issue?

Not applicable
Author

I added the Activities dimension in the aggr function as below

sum(aggr(

sum(HoursWorked) *

sum({<EmpName={"=sum(HoursWorked)>0"}>}  EmpTotalCost)

/ sum({<Activities= , EmpId={"=sum(HoursWorked)>0"}>} total <EmpName> HoursWorked)

,EmpName, Activities))

and it seems it is working. Please confirm if you agree. Also, if it is quick for you, any suggestion to implement the wage calculation into the script.

swuehl
MVP
MVP

Adding the pivot table dimension to the aggr() dimension list should do the trick.

Regarding the script modification, could you upload your excel sample file here?

Not applicable
Author

Thank you. Here it is.

swuehl
MVP
MVP

Isn't there a second table missing?

Not applicable
Author

Sorry...