Count Unique

[Anonymous]

Hi Experts,

Need advise here please.

Scenario:

one customer(formatted cli) called and answered 5 times by different agents (common user name). see below data.

Problem:

1. Count the unique number of customer (formatted CLI) that was answered more than once but not greater than 20 and tag it as Repeat Callers.

2. Count the unique number of customer (formatted CLI) that was answered only once and tag it as Single Callers.

In-Progress:

I've created an expression and Presented it thru Pivot

# Repeat Callers = num(count(aggr(if(Sum([#Answered])>1,Sum([#Answered]), null()) ,[Formatted CLI]))  ,vFormatCount0)

- num(count(aggr(if(Sum([#Answered])>20,Sum([#Answered]), null()) ,[Formatted CLI])) ,vFormatCount0)

# Single Callers = num(count(aggr(if(Sum([#Answered])=1,Sum([#Answered]), null()) ,[Formatted CLI])) ,vFormatCount0)

The above expression seems to be working when Formatted CLI is on the first dimension.

But when I change the Pivot and look for Common User Data, the expression is not showing the expected result.

Desired Result should be:

Common User Name	# Answered	# Repeat Callers	# Single Callers
Am	1	0	1
Av	1	0	1
Mi	1	0	1
Sh	1	0	1
Sr	1	0	1

Is there anything wrong with my expression? Please advise.

[andrey_krylov]

Hi Isabel.Your expressions are correct but there should be added the dimensions to aggregate by. Try this with no aggr

# Repeat Callers = num(Count({<[Formatted CLI] = {"=Sum([#Answered])>1"}-{"=Sum([#Answered])<20"} >} Distinct [Formatted CLI]), vFormatCount0)

# Single Callers = num(Count({<[Formatted CLI] = {"=Sum([#Answered])=1"} >}Distinct [Formatted CLI]), vFormatCount0)

[Anonymous]

Hi Andrey,

Thanks for your reply.

I tried the expression you've given but still did not give me right result.

'# Repeat Callers = num(Count({<[Formatted CLI] = {"=Sum([#Answered])>1"}-{"=Sum([#Answered])<20"} >} Distinct [Formatted CLI]), vFormatCount0)'

the # Repeat Callers* below is showing 5 on Alis (common user name), further drill down to customer (formatted cli) - see highlighted as sample.

the customer 'formatted cli' is actually handled by 2 different agents with only 1 answered each.

Alis should have 0 # Repeat Callers.

Thanks

[andrey_krylov]

Oh, I see. Then it should be aggregated and maybe this

# Repeat Callers = num(Sum(if(aggr(Sum([#Answered]),[Formatted CLI], [Agent]) > 1 and aggr(Sum([#Answered]),[Formatted CLI], [Agent]) < 20, 1, 0)),vFormatCount0)

# Single Callers = num(Sum(if(aggr(Sum([#Answered]), [Formatted CLI], [Agent]) = 1, 1, 0)),vFormatCount0)

Agent is the agent field you mentioned

[agigliotti · Accepted solution]

maybe this:

for repeat callers:

sum( aggr( if( Sum([#Answered]) > 1 and Sum([#Answered]) <= 20, 1, 0 ), [Common User Name], [Formatted CLI] ) )

for single callers:

=sum( aggr( if( Sum([#Answered]) = 1, 1, 0 ), [Common User Name], [Formatted CLI] ) )

I hope it helps.

[Anonymous]

Hi Andrea,

The customer (formatted CLI) '+612444' was answered by 2 different agents (common username).

This was the result for the expression you have given: (It is showing correct number when the pivot is set to common user name as the first dimension.

However, when i drag the Formatted CLI as first dimension, the expression did not give right result.

Desired result should be: 1 Repeat Caller and 0 Single Caller

[Anonymous]

Hi Andrey,

The customer (formatted CLI) '+612444' was answered by 2 different agents (common username).

After using the expression , # Repeat Callers = num(Sum(if(aggr(Sum([#Answered]),[Formatted CLI], [Agent]) > 1 andaggr(Sum([#Answered]),[Formatted CLI], [Agent]) < 20, 1, 0)),vFormatCount0),

It did not show the correct result.

should be 1 repeat caller

[agigliotti]

what you are saying is due to the Aggr function.

if you need to have the right result regardless of dimensions order i guess you have to change your data model.

[Anonymous]

thanks! I guess I cannot have both dimensions in 1 pivot with my data model