Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have a calculated field called [Score out of 6] to identify the score of employees.Now i want to calculate Total %ge by team that met a score of 4 or more. Example: team 1 has 10 employees in Week 10 of 2019. Of these 10 employees, 4 received a score of 4 or higher. The calculated metric for team 1 should be 40%.
Any help is highly appreciated.
Regards,
Joshua.
Hi,
Depending on how your data model works it might just be this;
sum(if([Score out of 6]>4,1,0))/count(Employee)
(Assuming you have a field Employee that you can count to give # employees by team…)
Cheers,
Chris.
Hi Chris,
Thanks for your reply. I tried it but its not working.The calculated expression [Score out of 6] is
=if(Num(sum({<reasoncode={5}>}EventDuration)/Sum(EventDuration),'###%')<=(Num(Median ( aggr(((sum({<reasoncode={5}>}EventDuration)/Sum(EventDuration))),AgentEnterprise_Desc,Agent_Team)),'##.##%')),1,0)
+
if(Num((sum({<eventtype={'3'}>}EventDuration)+sum({<eventtype={'5'}>}EventDuration) )/ (Sum(EventDuration)),'##.##%')>='68.0%',1,0)
+
If((((sum({<targettype={0},disposition={2},talktime={">0"}>}talktime) + sum({<targettype={0},disposition={2},talktime={">0"}>}worktime) + sum({<targettype={0},disposition={2},talktime={">0"}>}holdtime))/num(count({<targettype={0},disposition={2},talktime={">0"}>}talktime),'#,##0'))/60)<=
((Median ( aggr((((sum({<targettype={0},disposition={2},talktime={">0"}>}talktime)
+
sum({<targettype={0},disposition={2},talktime={">0"}>}worktime)
+
sum({<targettype={0},disposition={2},talktime={">0"}>}holdtime))/num(count({<targettype={0},disposition={2},talktime={">0"}>}talktime),'#,##0'))/60),AgentEnterprise_Desc,Agent_Team))-
Stdev ( aggr((((sum({<targettype={0},disposition={2},talktime={">0"}>}talktime)
+
sum({<targettype={0},disposition={2},talktime={">0"}>}worktime)
+
sum({<targettype={0},disposition={2},talktime={">0"}>}holdtime))/num(count({<targettype={0},disposition={2},talktime={">0"}>}talktime),'#,##0'))/60),AgentEnterprise_Desc,Agent_Team)))
)
or
((((sum({<targettype={0},disposition={2},talktime={">0"}>}talktime) + sum({<targettype={0},disposition={2},talktime={">0"}>}worktime) + sum({<targettype={0},disposition={2},talktime={">0"}>}holdtime))/num(count({<targettype={0},disposition={2},talktime={">0"}>}talktime),'#,##0'))/60)>(Median ( aggr((((sum({<targettype={0},disposition={2},talktime={">0"}>}talktime)
+
sum({<targettype={0},disposition={2},talktime={">0"}>}worktime)
+
sum({<targettype={0},disposition={2},talktime={">0"}>}holdtime))/num(count({<targettype={0},disposition={2},talktime={">0"}>}talktime),'#,##0'))/60),AgentEnterprise_Desc,Agent_Team))-
Stdev ( aggr((((sum({<targettype={0},disposition={2},talktime={">0"}>}talktime)
+
sum({<targettype={0},disposition={2},talktime={">0"}>}worktime)
+
sum({<targettype={0},disposition={2},talktime={">0"}>}holdtime))/num(count({<targettype={0},disposition={2},talktime={">0"}>}talktime),'#,##0'))/60),AgentEnterprise_Desc,Agent_Team)))
and (((sum({<targettype={0},disposition={2},talktime={">0"}>}talktime) + sum({<targettype={0},disposition={2},talktime={">0"}>}worktime) + sum({<targettype={0},disposition={2},talktime={">0"}>}holdtime))/num(count({<targettype={0},disposition={2},talktime={">0"}>}talktime),'#,##0'))/60)<= (Median ( aggr((((sum({<targettype={0},disposition={2},talktime={">0"}>}talktime)
+
sum({<targettype={0},disposition={2},talktime={">0"}>}worktime)
+
sum({<targettype={0},disposition={2},talktime={">0"}>}holdtime))/num(count({<targettype={0},disposition={2},talktime={">0"}>}talktime),'#,##0'))/60),AgentEnterprise_Desc,Agent_Team))))
,1,0)
+
If(((($(vACD_CSQ_Ag_HoldTime)/$(vACD_CSQ_Ag_CallsHandle))/60)<=(Median ( aggr(($(vACD_CSQ_Ag_HoldTime)/$(vACD_CSQ_Ag_CallsHandle)/60),AgentEnterprise_Desc,Agent_Team))-
Stdev ( aggr(($(vACD_CSQ_Ag_HoldTime)/$(vACD_CSQ_Ag_CallsHandle)/60),AgentEnterprise_Desc,Agent_Team))))
or
((($(vACD_CSQ_Ag_HoldTime)/$(vACD_CSQ_Ag_CallsHandle))/60)>
(Median ( aggr(($(vACD_CSQ_Ag_HoldTime)/$(vACD_CSQ_Ag_CallsHandle)/60),AgentEnterprise_Desc,Agent_Team))-
Stdev ( aggr(($(vACD_CSQ_Ag_HoldTime)/$(vACD_CSQ_Ag_CallsHandle)/60),AgentEnterprise_Desc,Agent_Team)))
and (($(vACD_CSQ_Ag_HoldTime)/$(vACD_CSQ_Ag_CallsHandle))/60)<=
(Median ( aggr(($(vACD_CSQ_Ag_HoldTime)/$(vACD_CSQ_Ag_CallsHandle)/60),AgentEnterprise_Desc,Agent_Team))))
,1,0)
+
if(num(num(count({<targettype={0},disposition={2},talktime={">0"},holdtime={">0"}>}talktime),'#,##0')/num(count({<targettype={0},disposition={2},talktime={">0"}>}talktime),'#,##0'),'##.##%')<='30.0%',1,0)
+
if(avg(Agent_Total_Login_Time_LoginID)>=6 and (Num(((sum({<reasoncode={21,2,1}>}EventDuration)/count(DISTINCT LocalDate))/60), '##.##'))<=62,1,
if(avg(Agent_Total_Login_Time_LoginID)>4.5 and avg(Agent_Total_Login_Time_LoginID)<6 and (Num(((sum({<reasoncode={21,2,1}>}EventDuration)/count(DISTINCT LocalDate))/60), '##.##'))<=22,1,
if(avg(Agent_Total_Login_Time_LoginID)<=4.5 and (Num(((sum({<reasoncode={21,2,1}>}EventDuration)/count(DISTINCT LocalDate))/60), '##.##'))<=11,1,0)))
Regards,
Joshua.
Hi Chris ,
Tried using the variable instead of expression but its not working
sum(if($(vScore)>4,1,0))/count(Employee)
Hi,
While I am sure it is genius, that is a horrible expression 😮.
My immediate thought is you should do something in the script to allow you to simplify the formula to make it easier to calculate/follow.
However you may get away with just wrapping another aggr around it, so possibly;
sum(if(AGGR(YOURFORMULA,DIMENSIONS)>4,1,0))/count(Employee)
But not exactly sure where the AGGR would need to sit & not sure I have anything that complex to hand to try to compare with. I would imagine the dimensions you would aggregate over are AgentEnterprise_Desc,Agent_Team similar to the existing AGGR you have.
Cheers,
Chris.