Set Analysis

[Anonymous]

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.

stalwar1‌

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

[Anonymous]

=sum(if((OverTime_HOURS/PLANNED_HOURS)>0.15,1))

[sunny_talwar]

May be like this:

Count(DISTINCT {<EMPLOYEE_ID = {"=OverTime_HOURS/PLANNED_HOURS >= 0.15"}>} EMPLOYEE_ID)

[sunny_talwar · Accepted solution]

Sample

[jagan]

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.

[Anonymous]

Bro, Instead of Count staff, can I do calculate the % of staff who worked > 15%. Please help for this expression also..

[sunny_talwar]

May be this:

Count(DISTINCT {<EMPLOYEE_ID = {"=OverTime_HOURS/PLANNED_HOURS >= 0.15"}>}EMPLOYEE_ID)/Count(DISTINCT EMPLOYEE_ID)

[jagan]

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)