Calculated columns

Calculated columns elements of a data set that can be combined to create a new column in the data set. For example, a data set might contain the columns “First Name” and “Last Name”. These two columns can be combined into a calculated column called “Full Name”.

Data as of 09 Aug 2022

Display name

Type

Formula text

ActivityType_Name STRING INSPECT [ActivityTypeId] CASE 1 THEN "Overtime" CASE 4 THEN "In Service" CASE 6 THEN "Assignment" CASE 7 THEN "Closed Service" CASE 8 THEN "Not Available" CASE 9 THEN "Available" CASE 10 THEN "Project" CASE 13 THEN "Exception" CASE 14 THEN "Lunch" CASE 15 THEN "Break" CASE 16 THEN "Overtime Multiskill Group" CASE 17 THEN "In Service Multiskill Group" CASE 18 THEN "Closed Service Multiskill Group" ELSE [ActivityType]
ActivityType_Transform STRING proper(replace("_"," ",[ActivityType]))
AgentAdherenceReportIsDeleted NUMBER true
AgentFullName STRING [firstName] & " " & [lastName]
AgentState_Transform STRING proper(replace("_"," ",[AgentState]))
applicationDurationSeconds NUMBER [applicationDuration]/1000
applicationInFocusDurationSeconds NUMBER [applicationInfocusDuration]/1000
ccr.EvalState STRING INSPECT [evalStateFK] CASE 0 THEN "Unscored" CASE 1 THEN "Scored" CASE 2 THEN "In-Progress" CASE 3 THEN "Needs Approval" CASE 4 THEN "Cannot Score" CASE 5 THEN "Needs Cannot Score Approval" ELSE NULL
CommenterFullName STRING [firstName] & " " & [lastName]
CommentKey NUMBER #coalesce function retruns first non-null value, so, in this case, if the parentkey is null then #the ID is used, otherwise the key is set to the ID. coalesce([parentCommentFK],[id])
CommentText STRING coalesce([text],'')
Contact Was Held NUMBER IF [recordingEventTypeId]=3 THEN 1 ELSE NULL
Contact Was Paused NUMBER IF [recordingEventTypeId]=4 THEN 1 ELSE NULL
Custom Data 01 STRING IF [dataExplorerKey]="1" THEN [data] ELSE null
Custom Data 01 Name STRING IF [dataExplorerKey]="1" THEN [metaDataFieldDisplayName] ELSE null
Custom Data 02 STRING IF [dataExplorerKey]="2" THEN [data] ELSE null
Custom Data 03 STRING IF [dataExplorerKey]="3" THEN [data] ELSE null
Custom Data 04 STRING IF [dataExplorerKey]="4" THEN [data] ELSE null
Custom Data 05 STRING IF [dataExplorerKey]="5" THEN [data] ELSE null
Custom Data 06 STRING IF [dataExplorerKey]="6" THEN [data] ELSE null
Custom Data 06 Name STRING IF [dataExplorerKey]="6" THEN [metaDataFieldDisplayName] ELSE null
Custom Data 07 STRING IF [dataExplorerKey]="7" THEN [data] ELSE null
Custom Data 08 STRING IF [dataExplorerKey]="8" THEN [data] ELSE null
Custom Data 09 STRING IF [dataExplorerKey]="9" THEN [data] ELSE null
Custom Data 10 STRING IF [dataExplorerKey]="10" THEN [data] ELSE null
Custom Data 11 STRING IF [dataExplorerKey]="11" THEN [data] ELSE null
Custom Data 12 STRING IF [dataExplorerKey]="12" THEN [data] ELSE null
Custom Data 13 STRING IF [dataExplorerKey]="13" THEN [data] ELSE null
Custom Data 14 STRING IF [dataExplorerKey]="14" THEN [data] ELSE null
Custom Data 15 STRING IF [dataExplorerKey]="15" THEN [data] ELSE null
Custom Data 16 STRING IF [dataExplorerKey]="16" THEN [data] ELSE null
Custom Data 17 STRING IF [dataExplorerKey]="17" THEN [data] ELSE null
Custom Data 18 STRING IF [dataExplorerKey]="18" THEN [data] ELSE null
Custom Data 19 STRING IF [dataExplorerKey]="19" THEN [data] ELSE null
Custom Data 20 STRING IF [dataExplorerKey]="20" THEN [data] ELSE null
Detractors NUMBER INSPECT [NPS Response] CASE 0,1,2,3,4,5,6 THEN 1 ELSE NULL
Duration in Seconds NUMBER [duration]/1000
Evaluation IsDeleted NUMBER [CallId] = NULL
EvaluatorFullName STRING [firstName] & " " & [lastName]
ForecastIsDeleted NUMBER true
ForecastScenarioName_Production STRING IF [ForecastScenarioId]=0 THEN "Production Forecast" ELSE [ForecastScenarioName]
FullName STRING [firstName] & " " & [lastName]
Group IsActive NUMBER if [Groupdeactivated] = toDate(2999,12,31,0,0,0) then false else true
Has Screen NUMBER IF [screenUploadState] > 0 then True ELSE False
Has Voice NUMBER IF [audioUploadState]>0 THEN True ELSE False
IsAgent STRING IF [WFMAgentId]=null then "false" else "true"
Negative Contacts - Sentiment NUMBER IF [Sentiment]=-1 THEN 1 ELSE null
Neutral Contacts - Sentiment NUMBER IF [Sentiment]=0 THEN 1 ELSE null
Normalized Contact Score NUMBER INSPECT [ScoringType] CASE 'Percentage' THEN [totalScore]/100 CASE 'Points' THEN divide([totalScore],[PointsFormQuestionPossibleScoreApplicable], null) ELSE NULL
NPS Response NUMBER IF lower(trim([metaDataFieldDisplayName]))="net promoter score" THEN numericValue([data]) ELSE NULL
Passives NUMBER INSPECT [NPS Response] CASE 7,8 THEN 1 ELSE NULL
Phrase End Seconds NUMBER [stop]/1000
Phrase Source STRING "Text Analytics"
Phrase Source STRING "Phonetics"
Phrase Source STRING "Transcription"
Phrase Start Seconds NUMBER [start]/1000
PointsFormQuestionPossibleScoreApplicable NUMBER INSPECT [EvalFormQuestionOptionTypeName] CASE 'Not Applicable' THEN 0 CASE 'NotApplicable' THEN 0 CASE 'NA' THEN 0 ELSE [PointsFormQuestionPossibleScore]
Postive Contacts - Sentiment NUMBER IF [Sentiment]=1 THEN 1 ELSE null
Predictive Detractors NUMBER INSPECT [PredictiveNPS] CASE 0,1,2,3,4,5,6 THEN 1 ELSE NULL
Predictive Detractors NUMBER INSPECT [netPromoterScore] CASE 0,1,2,3,4,5,6 THEN 1 ELSE NULL
Predictive Passives NUMBER INSPECT [PredictiveNPS] CASE 7,8 THEN 1 ELSE NULL
Predictive Passives NUMBER INSPECT [netPromoterScore] CASE 7,8 THEN 1 ELSE NULL
Predictive Promoters NUMBER INSPECT [PredictiveNPS] CASE 9,10 THEN 1 ELSE NULL
Predictive Promoters NUMBER INSPECT [netPromoterScore] CASE 9,10 THEN 1 ELSE NULL
Promoters NUMBER INSPECT [NPS Response] CASE 9,10 THEN 1 ELSE NULL
ScheduleActivityDurationSeconds NUMBER diffseconds([ActivityEnd],[ActivityStart])
ScheduleActivityIsDeleted NUMBER true
Scheduled Period Description STRING LET [timerange] = # format(<PATTERN "hh:mm">,[ActivityStart]) & " - " & format(<PATTERN "hh:mm">,[ActivityEnd]) (if hour([ActivityStart]) < 10 then "0" else "") & text(hour([ActivityStart])) & ":" & (if minute([ActivityStart]) < 10 then "0" else "") & text(minute([ActivityStart])) & " - " & (if hour([ActivityEnd]) < 10 then "0" else "") & text(hour([ActivityEnd])) & ":" & (if minute([ActivityEnd]) < 10 then "0" else "") & text(minute([ActivityEnd])) [timerange] & " = " & [ActivityType_Name]
Sentiment NUMBER IF [sentimentChannel1]=null && [sentimentChannel2]=null THEN null ELSE IF [sentimentChannel1]<>null && [sentimentChannel2]=null THEN [sentimentChannel1] ELSE IF [sentimentChannel1]=null && [sentimentChannel2]<>null THEN [sentimentChannel2] ELSE iferror(([sentimentChannel1]+[sentimentChannel2])/(abs([sentimentChannel1])+abs([sentimentChannel2])),0)
Sentiment NUMBER #Comment lines are the old formula used prior to 10.4.1 #IF [PredictiveSentimentChannelA]=null && [PredictiveSentimentChannelB]=null THEN null #ELSE # IF [PredictiveSentimentChannelA]<>null && [PredictiveSentimentChannelB]=null THEN [PredictiveSentimentChannelA] # ELSE # IF [PredictiveSentimentChannelA]=null && [PredictiveSentimentChannelB]<>null THEN [PredictiveSentimentChannelB] # ELSE # iferror(([PredictiveSentimentChannelA]+[PredictiveSentimentChannelB])/(abs([PredictiveSentimentChannelA])+abs([PredictiveSentimentChannelB])),0) (coalesce([PredictiveSentimentChannelA],[PredictiveSentimentChannelB])+coalesce([PredictiveSentimentChannelB],[PredictiveSentimentChannelA]))/2
Sentiment_Friendly STRING INSPECT [Sentiment] CASE 1 THEN "Positive" CASE -1 THEN "Negative" CASE 0 THEN "Neutral" ELSE null
Sentiment_Friendly STRING INSPECT [Sentiment] WHEN [] > 0.3 THEN "Positive" WHEN [] < -0.3 THEN "Negative" WHEN [] >= -0.3 && [] <= 0.3 THEN "Neutral" ELSE null
spoofCcrId NUMBER # adding the "times zero" reference to force the engine to recognize this as a numeric column NULL * 0
spoofConfidence NUMBER 0
spoofConfidence NUMBER 0
Team IsActive NUMBER if [Teamdeactivated] = toDate(2999,12,31,0,0,0) then false else true
URL STRING IF contains("<br>URL: ", [document]) THEN split([document],"<br>URL: ",2) ELSE null