Dan560
asked on
Formula help
Hi,
I have record select statement that looks this:
{opencall1.logdatex} in (((today-CDate(1970,01,01) )*24*60*60 ) - 2592000) to (today-CDate(1970,01,01))* 24*60*60
Basically the logdatex field is in unixtime. Everytime a call is logged in my helpdesk sytem the time of that call is loggd in that table. That formula calculates the lat 30 days.
The problem I am having is that I cannot use that in record select statement anymore. Part of my reports need to show average calls and when you have that select statement in it only calculates customers that have called and not the ones that havent.
So I need to apply that formula in a seperate formula field and not in the record select statement.
I have created this formula to calculate the number of calls in the last 30 days.
However it does not work, can anyone help?
{opencall1.logdatex} in (((today-CDate(1970,01,01) )*24*60*60 ) - 2592000) to (today-CDate(1970,01,01))* 24*60*60
and distinctcount ({opencall1.callref})
Thanks
I have record select statement that looks this:
{opencall1.logdatex} in (((today-CDate(1970,01,01)
Basically the logdatex field is in unixtime. Everytime a call is logged in my helpdesk sytem the time of that call is loggd in that table. That formula calculates the lat 30 days.
The problem I am having is that I cannot use that in record select statement anymore. Part of my reports need to show average calls and when you have that select statement in it only calculates customers that have called and not the ones that havent.
So I need to apply that formula in a seperate formula field and not in the record select statement.
I have created this formula to calculate the number of calls in the last 30 days.
However it does not work, can anyone help?
{opencall1.logdatex} in (((today-CDate(1970,01,01)
and distinctcount ({opencall1.callref})
Thanks
ASKER
Ok, that works, although I get an output of false or false
Is there a way I can count if the output is false then put 0 if it is true then put 1 ?
I just would like to then count all of the trues
Thanks
Is there a way I can count if the output is false then put 0 if it is true then put 1 ?
I just would like to then count all of the trues
Thanks
I assume that by ' average calls ' you must be calculating the average number of calls per customer.
If so, then you need a single value for the total number of customers.
To get that you can use a sql expression field which looks something like..
//%CustCount
(Select Count(customerid) from customertable)
This must be written in sql language used by your database.
You need to include the customer table in the report data source although it is not necessary to include any fields from it in the report.
You can then create a formula field to divide the total number of calls by this result
Something like..
Count ({opecall1.callid})/{%cust count}
If so, then you need a single value for the total number of customers.
To get that you can use a sql expression field which looks something like..
//%CustCount
(Select Count(customerid) from customertable)
This must be written in sql language used by your database.
You need to include the customer table in the report data source although it is not necessary to include any fields from it in the report.
You can then create a formula field to divide the total number of calls by this result
Something like..
Count ({opecall1.callid})/{%cust
ASKER
All I'm getting with the sql expressions is errors:
Error in compiling SQL Expression:
Database connection error: '42000:[MySQL][ODBC 3.51 Driver][mysqld-4.0.16-log] You have an error in your SQL syntax
(Select Count (cust_id) from opencall.cust_id)
Error in compiling SQL Expression:
Database connection error: '42000:[MySQL][ODBC 3.51 Driver][mysqld-4.0.16-log]
(Select Count (cust_id) from opencall.cust_id)
(Select Count (cust_id) from opencall)
Assuming your table is called opencall.
Assuming your table is called opencall.
ASKER
Unfortunatley that didnt work.
I am using mysql, if I drag the table I want to use it appears like this:
`opencall`.`cust_id`
I am using mysql, if I drag the table I want to use it appears like this:
`opencall`.`cust_id`
Where are you using the SQL?
mlmcc
mlmcc
ASKER
SQL expression section in formula editor
What have you entered as the SQL Expression?
A SQL Expression is jsut a SELECT statement that returns a sungle value.
mlmcc
A SQL Expression is jsut a SELECT statement that returns a sungle value.
mlmcc
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
and distinctcount ({opencall1.callref}) > 0