Forum Discussion
first call resolution
- Anonymous6 years ago
// T is the first dataset (not the second, aggregated). // There must be a Date table in the model // marked as Date table that joins to // T on [contact_date] and the filtering is // one-way. There also has to be a Customer dimension // in the model that joins on CustomerId to // T on [customer_id]. All columns of the fact table // T must be hidden and slicing is only allowed via // dimensions. If you want to slice by Issue, // you have to create a dimension Issue and join // to T on [issue_id]. In the code below I've assumed // that you won't be slicing by issues. // Then you can create measures: // This, for any slicing, will tell you // how many rows are visible in the current // context, which means "number of calls." [# Calls] = COUNTROWS( T ) // This will tell you the percentage of // customers visible in the current context // that got their issue resolved in the first // call. [First Call Resolution %] = var __existingCustIssueTable = SUMMARIZE( T, Customer[CustomerID], T[issue_id] ) var __custIssueWithOneCallResolution = SUMX( __existingCustIssueTable, ( [# Calls] = 1 ) * 1 ) var __custIssueTotal = COUNTROWS( __existingCustIssueTable ) RETURN DIVIDE( __custIssueWithOneCallResolution, __custIssueTotal )If you decide to introduce a separate Issue dimension, you'll need to adjust the code, especially what's under SUMMARIZE. Here's the code after the required changes for this scenario:
[First Call Resolution %] = var __existingCustIssueTable = SUMMARIZE( T, Customer[CustomerID], Issue[IssueID] ) var __custIssueWithOneCallResolution = SUMX( __existingCustIssueTable, ( [# Calls] = 1 ) * 1 ) var __custIssueTotal = COUNTROWS( __existingCustIssueTable ) RETURN DIVIDE( __custIssueWithOneCallResolution, __custIssueTotal )By the way, your specification of the problem is a bit misleading. You cannot just count customers in the current context. You have to calculate the pairs "customer-issue" to get the correct percentage.
Best
D
// T is the first dataset (not the second, aggregated).
// There must be a Date table in the model
// marked as Date table that joins to
// T on [contact_date] and the filtering is
// one-way. There also has to be a Customer dimension
// in the model that joins on CustomerId to
// T on [customer_id]. All columns of the fact table
// T must be hidden and slicing is only allowed via
// dimensions. If you want to slice by Issue,
// you have to create a dimension Issue and join
// to T on [issue_id]. In the code below I've assumed
// that you won't be slicing by issues.
// Then you can create measures:
// This, for any slicing, will tell you
// how many rows are visible in the current
// context, which means "number of calls."
[# Calls] = COUNTROWS( T )
// This will tell you the percentage of
// customers visible in the current context
// that got their issue resolved in the first
// call.
[First Call Resolution %] =
var __existingCustIssueTable =
SUMMARIZE(
T,
Customer[CustomerID],
T[issue_id]
)
var __custIssueWithOneCallResolution =
SUMX(
__existingCustIssueTable,
( [# Calls] = 1 ) * 1
)
var __custIssueTotal =
COUNTROWS(
__existingCustIssueTable
)
RETURN
DIVIDE(
__custIssueWithOneCallResolution,
__custIssueTotal
)
If you decide to introduce a separate Issue dimension, you'll need to adjust the code, especially what's under SUMMARIZE. Here's the code after the required changes for this scenario:
[First Call Resolution %] =
var __existingCustIssueTable =
SUMMARIZE(
T,
Customer[CustomerID],
Issue[IssueID]
)
var __custIssueWithOneCallResolution =
SUMX(
__existingCustIssueTable,
( [# Calls] = 1 ) * 1
)
var __custIssueTotal =
COUNTROWS(
__existingCustIssueTable
)
RETURN
DIVIDE(
__custIssueWithOneCallResolution,
__custIssueTotal
)
By the way, your specification of the problem is a bit misleading. You cannot just count customers in the current context. You have to calculate the pairs "customer-issue" to get the correct percentage.
Best
D
Hi Anonymous,
I modified your code and the result is what I got from my SQL. When I was solving the problem I wasn't exactly sure how I could measure this.
Thanks for your help!
Best regards,
Steven