topic Totals expression calculation issue in QlikView

Totals expression calculation issue

Wed, 08 Feb 2012 14:26:40 GMT

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.

Totals expression calculation issue

swuehl — Wed, 08 Feb 2012 15:01:33 GMT

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.

Totals expression calculation issue

Wed, 08 Feb 2012 15:53:10 GMT

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?

Totals expression calculation issue

Wed, 08 Feb 2012 16:03:43 GMT

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.

Totals expression calculation issue

swuehl — Wed, 08 Feb 2012 16:09:42 GMT

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?

Re: Totals expression calculation issue

Wed, 08 Feb 2012 16:15:28 GMT

Thank you. Here it is.

Totals expression calculation issue

swuehl — Wed, 08 Feb 2012 16:34:03 GMT

Isn't there a second table missing?

Re: Totals expression calculation issue

Wed, 08 Feb 2012 16:36:10 GMT

Sorry...