Calculating %

ajsjoshua — Wed, 26 Jun 2019 15:42:15 GMT

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.

Re: Calculating %

chrismarlow — Wed, 26 Jun 2019 17:59:49 GMT

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.

Re: Calculating %

ajsjoshua — Fri, 28 Jun 2019 12:09:14 GMT

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.

Re: Calculating %

ajsjoshua — Fri, 28 Jun 2019 12:33:19 GMT

Hi Chris ,

Tried using the variable instead of expression but its not working

sum(if($(vScore)>4,1,0))/count(Employee)

Re: Calculating %

chrismarlow — Fri, 28 Jun 2019 13:00:43 GMT

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.

topic Re: Calculating % in QlikView

Calculating %

Re: Calculating %

Re: Calculating %

Re: Calculating %

Re: Calculating %