topic Set Analysis in QlikView

Set Analysis

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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.

Anonymous — Fri, 23 Sep 2016 16:57:28 GMT

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

sunny_talwar — Fri, 23 Sep 2016 16:57:44 GMT

May be like this:

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

sunny_talwar — Fri, 23 Sep 2016 16:59:46 GMT

Sample

jagan — Fri, 23 Sep 2016 17:28:53 GMT

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 — Fri, 23 Sep 2016 17:30:47 GMT

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

sunny_talwar — Fri, 23 Sep 2016 17:31:59 GMT

May be this:

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

jagan — Fri, 23 Sep 2016 17:34:40 GMT

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)