Calculated columns
Calculated columns are 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.”
|
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 |