Calculated fields
Calculated fields are created from measures. They use existing measures to calculate new measures.
|
Display name |
Description |
Tags |
Formula text |
|---|---|---|---|
| # Contacts put on Hold | QM | # Calls put on hold: [Distinctcount of [Call].[CCRId] WHERE [Recording Event Type].[recordingEventTypeId]=3 && [Recording Event].[RecordingEvent.duration] > 0] | |
| # Contacts Taken | QM | [distinctcount of [Call].[CCRId] WHERE [Call].[wasAnswered]=1] | |
| # Inbound Contacts | QM | [distinctcount of [Call].[CCRId] WHERE [Call].[isInbound]=1] | |
| # Outbound Contacts | QM | [distinctcount of [Call].[CCRId] WHERE [Call].[isInbound]=0] | |
| # Paused Contacts | QM | # Paused Calls: [Distinctcount of [Call].[CCRId] WHERE [Recording Event Type].[recordingEventTypeId]=4 && [Recording Event].[RecordingEvent.duration] > 0] | |
| Agent IsActive | Analytics, QM | #old formula required a lot of work for the engine to evaluate #IF [Person].[activated] < now() && [Person].[deactivated] > now() THEN "true" else "false" LET [thisDay]=dateTrim(now(),"Day") #truncate current date and time to only YMD #[Person].[deactivated] should be 12/31/2999 for any active user. This value is changed when a user account is deactivated. IF dateTrim([Person].[deactivated],"Day") > [thisDay] THEN "true" ELSE "false" | |
| ATTR_CCRID_Link | Analytics, QM | IF (lookupUserAttribute("calabrio.ar.mode") = 'CAR') THEN NULL ELSE attribute("LINK","[system[location.protocol]]//[system[location.hostname]]/index.html#/recordings/"&text([Call].[CCRId])) & <FORECOLOR blue> | |
| ATTR_DURATION | Analytics, QM | <DURATION;PATTERN "mm:ss"> | |
| ATTR_DURATION_hhmmss | Analytics, QM | <DURATION;PATTERN "hh:mm:ss"> | |
| ATTR_kpiScoreColor | Analytics, QM | #Calabrio red - color("#A6192E") #Calabrio yellow - color("#FFC72C") #Calabrio green - color("#279989") <RANGE :0.7 <BACKCOLOR color("#A6192E"); FORECOLOR white>;RANGE 0.7+:1.0- <BACKCOLOR color("#FFC72C")>;RANGE 1.0: <BACKCOLOR color("#279989"); FORECOLOR white>> | |
| ATTR_NPS_ChartColors | Analytics, QM | INSPECT [Call].[NPS Response] CASE 0,1,2,3,4,5,6 THEN <FORECOLOR color("#A6192E")> CASE 7,8 THEN <FORECOLOR color("#FFC72C")> CASE 9,10 THEN <FORECOLOR color("#279989")> ELSE null | |
| ATTR_NPSColours | Analytics, QM | <RANGE -100:0 <FORECOLOR color("#A6192E")>;RANGE 1:50 <FORECOLOR color("#FFC72C")>;RANGE 51:100 <FORECOLOR color("#279989")>> | |
| ATTR_PredictiveNPS_ChartColors | Analytics | INSPECT [Predictive NPS Response2].[Predictive NPS Response] CASE 0,1,2,3,4,5,6 THEN <FORECOLOR color("#A6192E")> CASE 7,8 THEN <FORECOLOR color("#FFC72C")> CASE 9,10 THEN <FORECOLOR color("#279989")> ELSE null | |
| ATTR_SentimentChartColors | Analytics, QM | INSPECT [Sentiment] CASE "Negative" THEN <FORECOLOR color("#A6192E")> CASE "Neutral" THEN <FORECOLOR color("#FFC72C")> CASE "Positive" THEN <FORECOLOR color("#279989")> ELSE null | |
| Average Contact Duration | QM | iferror([callDurationSeconds]/[CountOfCallId],0) | |
| Average Contact Time | QM | iferror([callDurationSeconds]/[CountOfCallId],0) | |
| Average Evaluation Score | QM | [AVG of [Eval].[totalScore2]] | |
| Average Hold Time | QM | #[AVG of [RecordingEvent.duration] WHERE [Recording Event Type].[recordingEventTypeId]=3]/1000 [AVG of [SUM of [RecordingEvent.duration] BY [Call] WHERE [Recording Event Type].[recordingEventTypeId]=3]] / 1000 | |
| AVG Predictive NPS | Analytics | [AVG of [Predictive Net Promoter Score]] | |
| AVG Predictive Quality Score | Analytics | [AVG of [Predictive Quality Score]] | |
| Contact Duration Hours | QM | ifNull([callDurationSeconds],0) | |
| Contact Duration Seconds | QM | ifNull([duration],0)/1000 | |
| Contacts - Negative Sentiment | Analytics | [Distinctcount of [Call].[CCRId] WHERE [Call].[Sentiment] = "Negative"] | |
| Contacts - Negative Sentiment % | Analytics | iferror([Contacts - Negative Sentiment]/[Contacts - with Sentiment],null) | |
| Contacts - Neutral Sentiment | Analytics | [Distinctcount of [Call].[CCRId] WHERE [Call].[Sentiment] = "Neutral"] | |
| Contacts - Positive Sentiment | Analytics | [Distinctcount of [Call].[CCRId] WHERE [Call].[Sentiment] = "Positive"] | |
| Contacts - Positive Sentiment % | Analytics | iferror([Contacts - Positive Sentiment]/[Contacts - with Sentiment],null) | |
| Contacts - with Sentiment | Analytics | [Distinctcount of [Call].[CCRId] WHERE [Call].[Sentiment] <> null] | |
| Count of AgentId | Analytics, QM | [distinctcount of [Agent].[WFMAgentId]] | |
| Count Phrases | Analytics | [count of [Phrase Confidence].[Phrase].[Phrase]] | |
| CountOfCcrId | Analytics, QM | [distinctcount of [Call].[CCRId]] | |
| CountOfEvalID | QM | [distinctcount of [Eval].[EvalId]] | |
| CountOfFormID | QM | [distinctcount of [Form].[FormName]] | |
| CountOfGroupId | Analytics, QM | [distinctcount of [Group].[GroupId]] | |
| CountOfPersonID | Analytics, QM | [distinctcount of [Person].[PersonId]] | |
| CountOfTeamId | Analytics, QM | [distinctcount of [Team].[TeamId]] | |
| dCount Phrases | Analytics | [distinctcount of [Phrase].[Phrase]] | |
| EachUsedPhrase | Analytics | each(setToList([DistinctValues of [UsedPhrase] by only [Call]])) | |
| Evaluation Comments - Raw Text | QM | LET [comments]=[DistinctValues of [Evaluation Comment].[Evaluation Comment Text]] IF setSize([comments])=0 THEN "" ELSE format(<SET_START "* ";SET_END "";SET_SEPARATOR " * ">,[comments]) | |
| Evaluation Total Score Cohort | QM | #Test here for Percentage or Points to determine scale bands (0-100 or groups of 100's to 1000) INSPECT [Eval].[totalScore2] WHEN [] <11 THEN "01: 0-10" WHEN [] <21 THEN "02: 11-20" WHEN [] <31 THEN "03: 21-30" WHEN [] <41 THEN "04: 31-40" WHEN [] <51 THEN "05: 41-50" WHEN [] <61 THEN "06: 51-60" WHEN [] <71 THEN "07: 61-70" WHEN [] <81 THEN "08: 71-80" WHEN [] <91 THEN "09: 81-90" WHEN [] <101 THEN "10: 91-100" ELSE "99: Unknown" | |
| Evaluation Total Score Cohort Dual test | QM | #Test here for Percentage or Points to determine scale bands (0-100 or groups of 100's to 1000) LET [normalizedscore]=([Eval].[totalScore2]/[Form].[FormTotalPoints])*100 INSPECT [normalizedscore] WHEN [] <11 THEN "01: 0-10" WHEN [] <21 THEN "02: 11-20" WHEN [] <31 THEN "03: 21-30" WHEN [] <41 THEN "04: 31-40" WHEN [] <51 THEN "05: 41-50" WHEN [] <61 THEN "06: 51-60" WHEN [] <71 THEN "07: 61-70" WHEN [] <81 THEN "08: 71-80" WHEN [] <91 THEN "09: 81-90" WHEN [] <101 THEN "10: 91-100" ELSE "99: Unknown" | |
| KPI Actual | Analytics, QM | kpiActual(currentmeasurevalue()) | |
| KPI Actual Delta | Analytics, QM | kpiActualDelta(currentmeasurevalue()) | |
| KPI Actual Delta % | Analytics, QM | kpiActualDeltaPercent(currentmeasurevalue()) | |
| KPI Goal | Analytics, QM | kpiGoal(currentMeasureValue()) | |
| KPI Prior Actual | Analytics, QM | kpiPriorActual(currentmeasurevalue()) | |
| KPI Prior Score | Analytics, QM | kpiPriorScore(currentmeasurevalue()) | |
| KPI Score | Analytics, QM | kpiScore(currentMeasureValue()) | |
| KPI Score Delta | Analytics, QM | kpiScoreDelta(currentMeasureValue()) | |
| KPI Time Period Display | Analytics, QM | kpiTimePeriodDisplay(currentMeasureValue()) | |
| KPI Time Period Name | Analytics, QM | kpiTimePeriodName(currentMeasureValue()) | |
| KPI Time Period Range | Analytics, QM | kpiTimePeriodRange(currentMeasureValue()) | |
| KPI Values | Analytics, KPI, QM | "" | |
| Net Promoter Score Cohort | Analytics, QM | INSPECT [Call].[NPS Response] CASE 9,10 THEN "Promoters" CASE 7,8 THEN "Passives" CASE 0,1,2,3,4,5,6 THEN "Detractors" ELSE "Unknown" | |
| Normalized Predictive Contact Score | Analytics | # old formula # [Call].[Predictive Quality Score] / [Call].[Form].[FormTotalPoints] if [Call].[Form].[FormTotalPoints] = 0 then NULL else [Call].[Predictive Quality Score] / [Call].[Form].[FormTotalPoints] | |
| NPS | Analytics, QM | #LET [TotalResponses]=[Count of [Call].[NPS Response] WHERE [Call].[NPS Response]<>NULL] #LET [PromoterPCT] = [Promoters]/[TotalResponses] #LET [DetractorPCT] = [Detractors]/[TotalResponses] #([PromoterPCT]-[DetractorPCT])*100 #old formula above did not account for records from other data sets inflating count numbers and returning NPS above 100 LET [TotalResponses] = [Count of [Call].[NPS Response]] LET [TotalPromoters] = [Count of [Call].[NPS Response] WHERE [Call].[NPS Response] >= 9] LET [TotalDetractors] = [Count of [Call].[NPS Response] WHERE [Call].[NPS Response] <= 6] LET [PromoterPCT] = iferror([TotalPromoters]/[TotalResponses],null) LET [DetractorPCT] = iferror([TotalDetractors]/[TotalResponses],null) ([PromoterPCT]-[DetractorPCT])*100 | |
| NPS Response Distribution | Analytics, QM | [Call].[NPS Response] | |
| Paused Duration | QM | [SUM of [RecordingEvent.duration] WHERE [Recording Event Type].[recordingEventTypeId]=4]/1000 | |
| Paused Duration of a Contact | QM | ([SUM of [RecordingEvent.duration] WHERE [Recording Event Type].[recordingEventTypeId]=4]/1000)/[callDurationSeconds] | |
| Predictive Evaluation Total Score Cohort | Analytics | LET [predscore]=([Call].[Predictive Quality Score] / [Call].[Form].[FormTotalPoints])*100 INSPECT [predscore] WHEN [] <11 THEN "01: 0-10" WHEN [] <21 THEN "02: 11-20" WHEN [] <31 THEN "03: 21-30" WHEN [] <41 THEN "04: 31-40" WHEN [] <51 THEN "05: 41-50" WHEN [] <61 THEN "06: 51-60" WHEN [] <71 THEN "07: 61-70" WHEN [] <81 THEN "08: 71-80" WHEN [] <91 THEN "09: 81-90" WHEN [] <101 THEN "10: 91-100" ELSE "99: Unknown" | |
| Predictive Net Promoter Score Cohort | Analytics | INSPECT [Predictive NPS Response] CASE 9,10 THEN "Promoters" CASE 7,8 THEN "Passives" WHEN [] < 7 THEN "Detractors" ELSE "Unknown" | |
| Predictive NPS | Analytics | LET [TotalResponses]=[Count of [Call].[Predictive NPS Response] WHERE [Call].[Predictive NPS Response]<>NULL] LET [PromoterPCT] = [Predictive Promoters]/[TotalResponses] LET [DetractorPCT] = [Predictive Detractors]/[TotalResponses] ([PromoterPCT]-[DetractorPCT])*100 | |
| Question Label Text Group | Analytics, QM | [Question Label].[label] | |
| Question Text Group | Analytics, QM | [Question].[QuestionText] | |
| scoreContactsNegativeSentimentKPI | Analytics, KPI, QM | if kpiActual = null then null else maxOfList(0, (1 - kpiActual) / (1 - kpiGoal)) | |
| scoreNPSKPI | Analytics, QM | if kpiActual = null then null else (kpiActual + 100) / (kpiGoal + 100) | |
| scorePreferGreater | Analytics, QM | # old formula: #kpiActual / kpiGoal # # add check for divide by zero, return null if kpiGoal = 0 then error("Zero goal value", "Divide by zero error result from zero goal value") else kpiActual / kpiGoal | |
| scorePreferLesser | Analytics, QM | # old version kpiTarget / kpiActual #This works fairly well when the actual is near the target, but explodes when the #actual is low and gives a DivideByZero when the actual is zero. #It also doesn't decay very quickly when the actual is above the target. if kpiActual = null then null else maxOfList(0, 2 - kpiActual / kpiGoal) | |
| Search | Analytics, QM | text([Call].[CCRId]) | |
| Total Evaluation Score | QM | [SUM of [Eval].[totalScore2]] |