• Status: Solved
• Priority: Medium
• Security: Public
• Views: 377

# Display Customers that have double the average number of calls

Hi

I need to build a report that shows the customers that have double the average number of HD calls.
First I have figured out what the average is, my formula looks like this -

Average = distinctcount ({opencall.callref}) / distinctcount ({userdb.keysearch})

My formula that calculates what the double average looks like this -

{@Average} * 2

I then need to display the customers who have Double or more than this figure,however I 'm not sure how to do this, can someone help?

Thanks
0
Dan560
• 4
• 4
• 4
• +1
2 Solutions

Commented:
If the results are in the details section, suppress users that @customeraverage<(@totalaverage*2)
0

Commented:
Are you grouping on customer?

You could use a Group Selection formula like

distinctcount ({opencall.callref},{CustomerField}) / distinctcount ({userdb.keysearch},{CustomerField}) > distinctcount ({opencall.callref}) / distinctcount ({userdb.keysearch})

mlmcc
0

Author Commented:
Hi,

I'm grouping on userdb.keysearch which is actually the table where my customer ID's are stored.
0

Commented:
distinctcount ({opencall.callref},{userdb.keysearch }) / count ({userdb.keysearch},{userdb.keysearch }) > distinctcount ({opencall.callref}) / distinctcount ({userdb.keysearch})

mlmcc
0

Commented:
A couple of tweaks to mlmcc's group selection formula:

Count ({userdb.keysearch},{userdb.keysearch}) will give you the number of records for each customer.  I don't think you want to divide the number of calls for each customer ( DistinctCount ({opencall.callref}, {userdb.keysearch}) ) by that.  Actually, I don't think you want to divide that number by anything.

And if you want to see the customers with double the average or more, you need to multiply the overall average by 2.

DistinctCount ({opencall.callref}, {userdb.keysearch}) >=
DistinctCount ({opencall.callref}) / DistinctCount ({userdb.keysearch}) * 2

James
0

Commented:
Good catch.  I knew there was something wrong with the formula but none of my thoughts made sense.

mlmcc
0

Author Commented:
Thanks guys,

but "Count ({userdb.keysearch},{userdb.keysearch})" wont give me the number of records for each customer. It gives me the number 21. Where I would expect it to give me the total of customers that exsist within my system. Which is actually 245
0

Commented:
If you want all of them then just

Count({userdb.keysearch})

By adding the second part you are limiting it to the group.

mlmcc
0

Commented:
Like I said, Count ({userdb.keysearch},{userdb.keysearch}) will give you the number of records for each customer, but I don't think you even need that part of the formula.  My version of mlmcc's formula doesn't include that.

James
0

Author Commented:
Thanks when I put that in the group formula I get the following message -

" A constant expression is required here"

It then highlights
DistinctCount ({opencall.callref}, {userdb.keysearch})
0

Commented:
You accepted a solution after you posted that last message.  Do you still have a problem or did you get it figured out?

James
0

Author Commented:
Yes thanks it's working fine. I didnt get the chance to update EE.

0

Commented:
OK, good.  I just wanted to check.

James
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.