Cystal 8.5 - Need help wi/formula (or Running Total) - Not sure which! 1

[christinetjx]

I would appreciate any help with the following:

i have created a report that displays the # of calls logged each day, by the hour.
For example:

1/2/05
6am 4 calls
7am 8
8am 10
9am 6

1/3/05
6am 2 calls
7am 4
8am 2
9am 1

i am trying to create a formula that adds these calls but specific to each HOUR every day. So the formula would add # of calls logged every day at 6am, the # calls for 7am, etc.
i.e., using the above example, the total calls logged for:


6am is 6 calls
7am 12 calls
8am 12 calls
9am 7 calls

In my report, calls are Grouped first by day, then by the hour. Switching the Group to display hour first, then day did not make a difference.
I tried creating a Running total, but could not get it to work right. It kept adding the # of calls consecutively for each hour, instead of the specific hour of each day.

Thanks so much in advance....

christine

 

[lbass]

I think the problem you are having is based on your field, which I'm guessing is a datetime. Try creating a formula {@time}:

time({table.datefield})

Then insert a crosstab and use {@time} as your row field, and then highlight the row field->group options and choose "print on change of hour". Use no column field, and choose count of calls logged as the summary field.

Place the crosstab in the report footer.

-LB

 

[christinetjx]

Thank you it worked! I am a rookie and never used crosstab before.

 

[christinetjx]

Can i pls use this same thread to ask assistance w/the second part of this report. I need to add the total calls specific to each day of the month,i.e.

Week 1: Mon 102 calls
Tues 400 calls
Wed 500 calls

Week 2: Mon 208 calls
Tues 300 calls
Wed 100 calls

So this formula would add the # calls logged on Mondays (in the above case, 310 calls), then Tuesdays, etc.

I have continued to use the cross tab but i am having a problem w/the formula. I tried modifying the above formula a variety of ways, i.e.

WeekdayName(["Sun", "Mon", "Tues", ...])

{V_incidentsm1.open_time} WeekdayName (["Sun", "Mon", ...] )

DayOfWeek(Date(2004,11,28))

I do access Crystal help, I just don't seem to have enough background to understand what they try to explain. Thanks again. Christine

 

[lbass]

The formula you want is:

weekdayname(dayofweek({table.date}),true)

Use this as your rowfield, with no column field, and use count of {table.call} as your summary.

If you are trying to do this within the same crosstab, add the new formula and then drag it to the top position in the row field list. This will nest the times within each weekday.

-LB

 

[christinetjx]

YOU ROCK!

 

[christinetjx]

This is informational for myself so just respond if you have the time... but i'm trying to understand these formula's so i don't have to ask again...

This formula you had me use:
weekdayname(dayofweek({table.date}),true)

i went into Crystal Help to see if i could try to understand it better and i found this...

WeekdayName (weekday, abbreviate)

So in the formula *I* used above, we are saying, first specify the week DAY (weekdayname) but why are we repeating the request again with 'dayofweek' right after that? i'm assuming the 'true' part, is saying i want the abbreviated weekday name. Thank you again for all the help.

 

[kskid]

The DayOfWeek function returns a number for each day (i.e. 1 for Sunday through 7 for Saturday. WeekDayName requires a number to translate to a given Day name.

Cheers,

-LW

 

[christinetjx]

Thank you everyone, I am asking assistance once again with this same report.

My report currently displays the total # of calls logged by the hour. It is then grouped into calls that were fixed immediately and calls that were escalated. Next to each sum, i have it displayed in % also. For example:

1/2/05
8AM 48 calls in total
Fixed 35 73%
Escalated 13 27%

9AM 13
Fixed 7 54%
Escalated 6 46%

1/3/05
8AM 22 calls in total
Fixed 16 73%
Escalated 6 27%

9AM 57
Fixed 35 61%
Escalated 22 39%

I do this for a full week.
At the end of the report is a cross tab with 3 columns. One column displays the total fixed calls logged for that particular hour that week, the second column displays total escalated calls and a 3rd column shows the total # of calls logged this particular hour, this week. So using the above example:

FIXED ESCALATED TOTAL
8am 51 19 70
9am 42 28 70

What i would like to see in this cross tab (if possible) is an additional column that shows the fixed/escalated figures in percent (just as they are displayed earlier in the chart). As always, i appreciate the help... Christine

 

[lbass]

This probably should have been a new thread. You could do this one of two ways. First, you could create a series of running totals that define each "cell" without using an inserted crosstab. Using the running total expert, each running total (let's call this one {#8fixed}) would use sum of {table.callslogged}, a formula in the evaluation section:

hour({table.datetime}) = 8 and
{table.calltype} = "Fixed"

...and reset = never.

Then the percentage would be a formula like {@percent8fixed}:

{#8fixed} % {#8total}

Not sure there IS a field called {table.calltype} or if you are identifying "Fixed" and "Escalated" by some datediff formula, but assuming there is such a field, if you really want to use an inserted crosstab, you could potentially use SQL expressions to get the percentages. Create the following SQL expressions:

//{%sumbyhrtype}:
(select sum(AKA.`callslogged`) from Table AKA where
{fn WEEK(AKA.`DateTime`)} = {fn WEEK(Table.`DateTime`)} and
{fn HOUR(AKA.`DateTime`)} = {fn HOUR(Table.`DateTime`)} and
AKA.`CallType` = Table.`CallType`)

//{%sumbyhr}:
(select sum(AKA.`callslogged`) from Table AKA where
{fn WEEK(AKA.`DateTime`)} = {fn WEEK(Table.`DateTime`)} and
{fn HOUR(AKA.`DateTime`)} = {fn HOUR(Table.`DateTime`)})

In each formula, substitute your table name for "Table" wherever it appears, and substitute your exact field names for "callslogged", "datetime", and "calltype". Leave "AKA" as is, since it represents an alias field name. If you have other selection criteria for your report, these might need to be built into the formulas as well.

Then create a formula {@hrspercentoftotal}:

if {%sumbyhr} <> 0 then
{%sumbyhrtype} % {%sumbyhr}

Add {@hrspercentoftotal} as a summary field in your crosstab. After exiting the crosstab expert, change the summary to a maximum and then click on the % icon to get the correct display. You need to use a maximum since the formula itself is the correct result, so you don't want it accumulated in the crosstab. Also, to avoid a Dr. Watson, it is best to change the summary operation after exiting the crosstab expert.

-LB

 

[christinetjx]

lbass thank you for this, i have taken this verbatim and tried creating the running total piece as you suggested.
Below is an excerpt from above. I have marked in caps (at the very bottom) where i'm having the problem:

"First, you could create a series of running totals that define each "cell" without using an inserted crosstab. Using the running total expert, each running total (let's call this one {#8fixed}) would use sum of {table.callslogged}, a formula in the evaluation section:

hour({table.datetime}) = 8 and
{table.calltype} = "Fixed"

...and reset = never.

Then the percentage would be a formula like {@percent8fixed}:

{#8fixed} % {#8total} <--THIS IS WHERE I AM HAVING A PROBLEM

What is this '{#8total}'???

 

[lbass]

{#8total} would be another running total that is the total for that hour without a breakout by type, so the evaluation formula would be just:

hour({table.datetime}) = 8

In your sample data below, you would have 6 running totals, and "70" would be the result for both {#8total} and {#9total}.

FIXED ESCALATED TOTAL
8am 51 19 70
9am 42 28 70

-LB