Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have the data like this. Here I'm calculating the %Over time. = OverTime_Hrs/PlannedHrs
Now I'm trying to calculate the Number of staff who worked more than 15% in a text box. Please advise.
EMPLOYEE_ID | PLANNED_HOURS | ACTUAL_HOURS_DOC | OverTime_HOURS |
62143 | 440 | 595 | 155 |
62238 | 440 | 588 | 148 |
62626 | 440 | 467 | 27 |
62735 | 440 | 547 | 107 |
62793 | 440 | 600.2 | 160.2 |
63465 | 440 | 499.5 | 59.5 |
63563 | 440 | 93 | 0 |
63898 | 440 | 447 | 7 |
4242 | 440 | 487.5 | 47.5 |
64366 | 440 | 442 | 2 |
64829 | 440 | 577 | 137 |
65120 | 440 | 517.5 | 77.5 |
=sum(if((OverTime_HOURS/PLANNED_HOURS)>0.15,1))
May be like this:
Count(DISTINCT {<EMPLOYEE_ID = {"=OverTime_HOURS/PLANNED_HOURS >= 0.15"}>} EMPLOYEE_ID)
Sample
HI Vinay,
Try like this
Count(DISTINCT {<EMPLOYEE_ID = {"=Sum(OverTime_HOURS)/Sum(PLANNED_HOURS) >= 0.15"}>}EMPLOYEE_ID)
OR try using Aggr() like below
Sum(Aggr(If(Sum(OverTime_HOURS)/Sum(PLANNED_HOURS) >= 0.15, 1, 0), EMPLOYEE_ID))
Regards,
Jagan.
Bro, Instead of Count staff, can I do calculate the % of staff who worked > 15%. Please help for this expression also..
May be this:
Count(DISTINCT {<EMPLOYEE_ID = {"=OverTime_HOURS/PLANNED_HOURS >= 0.15"}>}EMPLOYEE_ID)/Count(DISTINCT EMPLOYEE_ID)
Hi,
Try like this
Count(DISTINCT {<EMPLOYEE_ID = {"=Sum(OverTime_HOURS)/Sum(PLANNED_HOURS) >= 0.15"}>}EMPLOYEE_ID)/Count(Distinct EMPLOYEE_ID)
OR try using Aggr() like below
Sum(Aggr(If(Sum(OverTime_HOURS)/Sum(PLANNED_HOURS) >= 0.15, 1, 0), EMPLOYEE_ID))/Count(Distinct EMPLOYEE_ID)