Simple Average for Groups 2

[MarsianStar]

Hello All,

Using Crystal XI and Oracle.

Ultimate Goal: A call center environment. I would like to calculate the "average" calls we get by weekday, then summarized hourly, then put all of that in a table just like a cross tab.

Tried but Failed: A crosstab, It puts up the sums of all days or perday, but I need averages not sums.

So far: I grouped all selected records by DateReceived and summarized it weekly. Then grouped by weekday and inserted distinctcount of CallId field. So far it looks like this:

The week of Sunday 2 Sept. 2007

Monday 115 Tickets Received
Tuesday 172 Tickets Received
Wednesday 185 Tickets Received
Thursday 119 Tickets Received
Friday 105 Tickets Received
Saturday 75 Tickets Received
Sunday 80 Tickets Received

Any ideas?

 

[MarsianStar]

By the way, I am a newbie in Crystal. Please forgive my lack of knowledge.

 

[lbass]

Please show an example of how your resulting crosstab should look, using sample data. Do you mean that you want the averages per weekday across a date range, eg., average for all Mondays, etc.? How do the hourly averages fit into this? Hourly averages within all Mondays?

-LB

 

[MarsianStar]

Thanks Lb

I want it to look like this

Mon Tue Wed Thu Fri Sat Sun
00:00 12 16
01:00 14 19
02:00 5 9
03:00 7 12
04:00 25 30
.
.
.
23:00

Where the 12 on Monday is the average number of tickets created in the first hour of the day. So from this table I should tell that Tuesdays are busier than Mondays. I did not fill out the rest of the days as I am afraid I will loose the formatting.

 

[lbass]

Since you want to average distinctcounts, I think you would have to use a command (new blank report->your datasource->add command) like:

Select count(distinct "table"."Call_ID") "CntDistID",
to_char("table"."date", 'DD-MM-YY') "Date",
to_char("table"."date", 'HH24') "Hour of Day"
FROM "Owner"."table" "table"
WHERE "table"."date" >= '01-NOV-07' and
"table"."date" < '01-Dec-07'
Group by
to_char("table"."date",'DD-MM-YY'),
to_char("table"."date",'HH24')

In the above, I limited the records to the month of November. Once you have the command working you can create a formula {@dayofweek} in the main report:

dayofweek(date({command.date}))

Then insert a crosstab in the report header that uses {@dayofweek} as the column, {command.hour of day} as the row field, and average of {command.CntDistID} as the summary field.

In the crosstab expert, while the column field is highlighted, click on group options->options->customize group name->use a formula->x+2 and enter:

weekdayname(dayofweek(date({command.date})))

-LB

 

[MarsianStar]

Thanks again LB,
I have never used SQL commands before so I am going to customize it and try it. The one thing I have mention is that "table"."date" is not a date field. It is a string. In Crystal I use date({calllog.receiveddate}) to be able to further manipulate it.

 

[lbass]

Try:

Select count(distinct "table"."Call_ID") "CntDistID",
to_char(to_date("table"."date"), 'DD-MM-YY') "Date",
to_char(to_date("table"."date"), 'HH24') "Hour of Day"
FROM "Owner"."table" "table"
WHERE to_date("table"."date") >= '01-NOV-07' and
to_date("table"."date") < '01-Dec-07'
Group by
to_char(to_date("table"."date"),'DD-MM-YY'),
to_char(to_date("table"."date"),'HH24')

-LB

 

[MarsianStar]

I tried the code but I am getting errors, does this look OK?

Select count(distinct "CALLLOG"."CALLID") "CntDistID",
to_char(to_date("CALLLOG"."RECVDDATE"), 'DD-MM-YY')"Date",
to_char(to_date("CALLLOG"."RECVDDATE"), 'HH24') "Hour of Day",
FROM "Owner"."table" "table"
WHERE to_date("CALLLOG"."RECVDDATE") >= '01-SEP-07' and
to_date("CALLLOG"."RECVDDATE") < '01-Dec-07'
Group by
to_char(to_date("CALLLOG"."RECVDDATE"),'DD-MM-YY'),
to_char(to_date("CALLLOG"."RECVDDATE"),'HH24')

I do not know how to fixup the From clause.

 

[lbass]

Select count(distinct "CALLLOG"."CALLID") "CntDistID",
to_char(to_date("CALLLOG"."RECVDDATE"), 'DD-MM-YY') "Date",[Red][add a space before the alias][/Red]
to_char(to_date("CALLLOG"."RECVDDATE"), 'HH24') "Hour of Day",
FROM [red]"CALLLOG" "CALLLOG" [/red]
WHERE to_date("CALLLOG"."RECVDDATE") >= '01-SEP-07' and
to_date("CALLLOG"."RECVDDATE") < '01-Dec-07'
Group by
to_char(to_date("CALLLOG"."RECVDDATE"),'DD-MM-YY'),
to_char(to_date("CALLLOG"."RECVDDATE"),'HH24')

I'm not sure whether you have to specify the owner, so just try the above with the two changes I made in red. Go into database->show SQL query for a report that uses the same table, and see if in the FROM section there is an owner in front of the table name so that it reads like:

"Owner"."CALLLOG" "CALLLOG"

If there is, then add that into the FROM clause as in my first post.

-LB

 

[MarsianStar]

How can I do a step by step check to see which line is giving the errors?
Is there a way to check that the CALLLOG.RECVDDATE convert properly? because I think that is where the problem is.

 

[lbass]

What is the error message you are getting? There isn't a simple way to determine what is causing the error. You could try a new report and just try using:

Select to_char(to_date("CALLLOG"."RECVDDATE"), 'DD-MM-YY') "Date"
FROM "CALLLOG" "CALLLOG"

Then see how it displays.

-LB

 

[MarsianStar]

If I run the entire command I get

Details: HY000:[Oracle][ODBC][Ora]ORA-00936: missing expression
[Database Vendor Code: 936]

If I run
Select to_char(to_date("CALLLOG"."RECVDDATE"), 'DD-MM-YY') "Date"
FROM "CALLLOG" "CALLLOG"

I get
Details: HY000:[Oracle][ODBC][Ora]ORA-01861: literal does not match format string.
[Database Vendor Code: 1861]

 

[lbass]

Please test this:

Select to_char(to_date("CALLLOG"."RECVDDATE",'DD-MM-YY'), 'DD-MM-YY') "Date"
FROM "CALLLOG" "CALLLOG"

-LB

 

[MarsianStar]

I get that error
Details: HY000:[Oracle][ODBC][Ora]ORA-01861: literal does not match format string.
[Database Vendor Code: 1861]

 

[lbass]

I don't really think I can be of much more assistance here. It's trial and error for me when figuring out how to write this sort of thing. You could try:

Select to_char(to_date("CALLLOG"."RECVDDATE",'DD-MON-YYYY'), 'DD-MON-YYYY') "Date"
FROM "CALLLOG" "CALLLOG"

Maybe you could ask in an Oracle forum.

-LB

 

[MarsianStar]

I do appreciate all the help you give LB, thanks.
I am reading up on sql commands right now. May be I can get it pulled and do calculations in crystal right after. Do you think that this is doable?

 

[lbass]

If you need to use a distinctcount for the average, then I think you have to do it this way. If a count would give the correct results, then you could do this without the command.

-LB

 

[MarsianStar]

Thanks again LB

 

[MarsianStar]

OK, I calculated the difference between distinct count and count, and found that it represents less than 1 percent. How can I do the average on the count instead?

 

[lbass]

Okay, instead of the earlier approach, I think you can do a manual crosstab this way. First convert your date to a datetime field {@datetime} in a formula:

cdatetime({CALLLOG.RECVDDATE})

Then, create a formula {@Hour}:

hour({@datetime})

Then insert a Group #1 on {@Hour}, and a group #2 on {@datetime} on change of week. Then create a a formula {@Null} by opening and saving a new formula without entering anything. Next create seven formulas like this:

//{@Sun}:
if dayofweek({@dateetime}) = 1 then
{CALLLOG.CALLID} else
tonumber({@Null}) //if callID is a string, remove the tonumber()

//{@Mon}:
if dayofweek({@dateetime}) = 1 then
{CALLLOG.CALLID} else
tonumber({@Null})

//etc.

Then create these formulas:
//{@reset} to be placed in GH1 (hour):
whileprintingrecords;
numbervar Sun := 0;
numbevar Mon := 0;
numbervar Tue := 0; //etc.
numbervar cnt := 0;

//{@accum} to be placed in GH#2 (week):
whileprintingrecords;
numbervar Sun := Sun + distinctcount({@Sun},{@datetime},'weekly');
numbevar Mon := Mon + distinctcount({@Mon},{@datetime},'weekly');
numbervar Tue := Tue + distinctcount({@Tue},{@datetime},'weekly'); //etc.
numbervar cnt := cnt + 1;

Place the following display formulas in the GF#1 horizontally. If you added the conditional formulas {@Sun}, {@Mon}, etc., to the detail section, you will already have the appropriate column headers.

//{@DisplaySun} to be placed in GF#1:
whileprintingrecords;
numbervar Sun;
numbervar cnt;
if cnt > 0 then
Sun/cnt

//{@DisplayMon} to be placed in GF#1:
whileprintingrecords;
numbervar Mon;
numbervar cnt;
if cnt > 0 then
Mon/cnt

Repeat for other days of the week. Then suppress the group headers and detail section. Drag the {@Hour} group name into the group #1 footer.

I should have suggested this at first. Sorry.

-LB