• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 371
  • Last Modified:

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
Asked:
Dan560
  • 4
  • 4
  • 4
  • +1
2 Solutions
 
snurkerCommented:
If the results are in the details section, suppress users that @customeraverage<(@totalaverage*2)
0
 
mlmccCommented:
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
 
Dan560Author Commented:
Hi,

I'm grouping on userdb.keysearch which is actually the table where my customer ID's are stored.
0
New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

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


mlmcc
0
 
James0628Commented:
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
 
mlmccCommented:
Good catch.  I knew there was something wrong with the formula but none of my thoughts made sense.

mlmcc
0
 
Dan560Author 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
 
mlmccCommented:
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
 
James0628Commented:
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
 
Dan560Author 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
 
James0628Commented:
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
 
Dan560Author Commented:
Yes thanks it's working fine. I didnt get the chance to update EE.

0
 
James0628Commented:
OK, good.  I just wanted to check.

 James
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 4
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now