10 Replies Latest reply: Feb 8, 2012 9:08 AM by jkizanis

Totals expression calculation issue

Hello,

In the QlikView file attached, I would like to see only employees EMP1 to EMP4 and EMP6 to EMP9 because employees EMP5 and EMP10 to EMP18 haven't worked and "Total Hours Worked" is zero.

Furthermore, the last line that calculates the totals, the "Total Employee Cost" and "Employee Hourly Cost" should be calculated only by the employees that have hours. The current calculations are wrong.

Thanks,

--John

• Totals expression calculation issue

Try using

=if("Total Employee Hours",sum(EmpTotalCost),0)

as expression for Total Employee Cost.

Since you have already enabled the option "suppress zero values" in presentation tab, this will limit your chart to the required lines.

• Totals expression calculation issue

Hi,

This solved the first part of my problem. Thank you.

I still have an issue with the calculated totals. the last line that calculates the totals, the "Total Employee Cost" and "Employee Hourly Cost" should be calculated only by the employees that have hours. The current calculations are wrong.

• Totals expression calculation issue

I see.

You are using a pivot table, so your totals will be evaluated as expression total. You could use a straight table with total mode sum of rows, but that won't help you with the hourly cost total. So you probably need to use advanced aggregation, like

=sum(aggr(if(sum(HoursWorked),sum(EmpTotalCost),0),EmpName))

for the Total Employee Cost expression and

=sum(aggr(if(sum(HoursWorked), sum(EmpTotalCost)),EmpName))

/sum({<Activities=>} total <EmpName> HoursWorked)

for the Hourly Cost.

Hope this helps,

Stefan

• Totals expression calculation issue

Well , it could be somewhat more simple and more efficient:

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

as Total Employee Cost

and

="Total Employee Cost" / sum({<Activities=>} total <EmpName> HoursWorked)

as Hourly Cost (you need to label your first expression "Total Employee Cost" to be able to reference it like this.

• Totals expression calculation issue

Thank you. Almost there. The only problem is that if I select an Activity (e.g. Add a List Box with "Activities") and then select for example "Cu A4" you will notice that the total for "Employee Hourly Cost" is miscalculated but the individual calculations are correct. If you know how to overcome this problem I will be very thankful.

• Re: Totals expression calculation issue

Hi,

Good luck!

Rainer

• Re: Totals expression calculation issue

Thank you Rainer. This expression has a problem with the total.

Here is an example. I select EMP1 and EMP3 and Activity "Cu A4". The Employee Hourly Cost should be calculated as follows:

(3201+1072,93) Euro / (154+72,5) Hours = 18,86 Euro/Hour.Instead your formula produces 35,53 Euro/Hour. Let me know if you can do something about it.

• Totals expression calculation issue

Try for hourly costs:

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

• Re: Totals expression calculation issue

Thank you very much! This worked fine!

• Re: Totals expression calculation issue

Hi John,

now you will have only employees in your pivot table with an entry "Hours Worked".

Step one to get the result you need.