7 Replies Latest reply: Feb 8, 2012 11:36 AM by jkizanis RSS

    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.

        • Totals expression calculation issue
          Stefan Wühl

          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.