Totals expression calculation issue

[#1]

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

[swuehl]

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.

[#3]

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.

[swuehl]

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

[swuehl]

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.

[#6]

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.

[#7]

Hi,

please see your modified example (AGGR funktion).

Good luck!

Rainer

[#8]

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.

[swuehl · Accepted solution]

Try for hourly costs:

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

[#10]

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.

What do you need additionally?

Rainer