Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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.
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?
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.
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?
Thank you. Here it is.
Isn't there a second table missing?
Sorry...