Call Centre Reporting 1

[Dandas]

Hi.

Like alot of people I am relatively new to crystal and I use it for call centre reporting off a Rockwell switch.

I am trying to roll up into 1/2 hour periods a whole lot of event data that is recorded when an agent punches a code into their phone. So I need to get the time format to end in either 00 or 30. At the moment the data that comes out is inf the hh:mm:ss. I was thinking I need to break out the minutes and round them to the period ending either 00 or 30 which I did by using.

if (TrimRight ({@StartTime} [4 to 5])) >= "1" and

(TrimRight ({@StartTime} [4 to 5])) < &quot;31&quot;

Then

&quot;30&quot;

else

&quot;00&quot;

Now I need to get the hh: number to increase by 1 if the :mm: number ends with 00 so that it will be 15:00 or 08:00. This is where I am stuck. I can't work out how to increase a number (which is a caculated field) by 1.

I have tried a few formulas eg.

sum(1,TrimLeft ({@StartTime} [1 to 2]))

But I keep getting error messages telling me things like a field has to be entered where the 1 is and I can't just go and add a new field to the data base and give it a constant value of 1.

Any suggestions of how to increase a field by 1???

Thanks

 

[synapsevampire]

I think that the field is a string, so convert it and add 1, then convert it back to a string:

if (TrimRight ({@StartTime} [4 to 5])) >= &quot;1&quot;
and
(TrimRight ({@StartTime} [4 to 5])) < &quot;31&quot; then
totext(val(Left({@StartTime},2))+1,0)
else
Left({@StartTime},2)

Something like that.

You use sums to add multiple values, in which case you'd need to convert this string field to a number anyway, and then the resulting sum could have +1 added to it in a formula, and then use Totext() on it to convert it back to text.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Dandas]

You totally Rock !!!

That was so close. Only problem is that it is rounding up the hours when the :mm: are <= than 31. I need it to basically say if it is after 08:30 then it will be counted in the period ending 09:00. So I need the hours to go up when it is the opposite to what you wrote.

eg. 08:11 should be 08:30

08:37 should be 09:00

I had a little play with it and could not get it to work (needless tosay I guess it comes down to experience -or lack of it-).


Any suggestions while I keep trying?

BTW I had a look at some more of your responses synapsevampire.....Impresive !!!

 

[Ngolem]

You are rounding up your time....this creates a whole host of problems that don't exist if you round down the time.

with rounding up you must test if an hour needs to be incremented...and if that happens does the day need to be incremented....next comes month and finally if your event is near the stroke of midnight Dec 31 you could have a new year too.

Also you can ignore seconds when rounding down

All that is eliminated by rounding down.

Please let us look at your formula {@StartTime}

If I have this correct you just want to round to the nearest 30 sec...so why are we doing string manipulation here??

for example: if we have a datetime variable and we want to round down it is a simple formula like this will work fine

@RoundingDownToHalfMinute

WhilePrintingRecords;
dateTimeVar test := {table.dateTimeValue};

if minute(test) < 30 then
test := datetime(year(test),month(test),day(test),
hour(test),0,0)
else
test := datetime(year(test),month(test),day(test),
hour(test),30,0);

test;

there...nice and simple rounding down. But as I said earlier...why are you converting the datetime to a string...is it only for the rounding?? Jim Broadbent

 

[Dandas]

What can I say. Another legend at the game.

I actually just finished having a bit of a play with it and managed to sort it out. I used

if (TrimRight ({@StartTime} [4 to 5])) >= &quot;31&quot; then
totext(val(Left({@StartTime},2))+1,0)
else
Left({@StartTime},2)

Just so you know the reason I needed to round it was simply to create a unique key so that i could run intraday reports on a half hour basis.

Thanks so much for your help guys, it is much appreciated.

 

[SmpTraining]

With a slight amendment to Jim's formula you get all the benefits of the simpler formula and it works exactly as you have asked for:

dateTimeVar test := DateTime(2002,07,01, 08,37,00);
if minute(test) < 30 then
test := datetime(year(test),month(test),day(test),
hour(test),30,0)
else
test := datetime(year(test),month(test),day(test),
hour(test)+1,0,0);
test;

Jim, if we add to the hour field to round up it will automatically take care of changes around midnight, end of year etc. This formula is the way forward... Steve Phillips, Crystal Consultant

 

[Naith]

I'm a bit confused here.

How did the date get into the game? Dandas said in his first post that his field is a character string in the format &quot;hh:mi:ss&quot;.

I don't know what kind of business day you have, Dandas - if it's just 9-5, all good. But, if it's 23:31 you'll get a value of 24 o'clock.

You could catch the 24 and convert it to 0 in your formula, but I like the idea of converting the string field to time (being as that's what it is), and working with that.

Let's nab Jim's and Steve's formula, strip the dates out, put an 11pm catcher in there so it doesn't fall over, and have a look at that:


TimeVar test := Time({@starttime});

if minute(test) < 30 then
test := time(
hour(test),30,0)
else
if hour(test) < 23 then
test := time(
hour(test)+1,0,0)
else test := time(
hour(test)-23,0,0);
test;

Naith

 

[Ngolem]

Steve -

&quot;Jim, if we add to the hour field to round up it will automatically take care of changes around midnight, end of year etc. This formula is the way forward...&quot;

Not by my test Steve...if the hour is 23 and you add 1 to it making 24...I get an error that the hour must be between 0 and 23.

The similar error would occur for day and month as well...rounding down saves these hassels

Naith- the reason why I included the day/month/year was for completeness....I have no idea the format of the field since it is camoflagued in the formula {@Start}...
your formula also has the problem of having hour increased to 24

I will stick to my guns and say that Rounding DOWN is the way to go UNLESS you are prepared to build-in a bunch of tests to increment to a new day, potentially new month and even year. Jim Broadbent

 

[Naith]

J.B.

I agree that rounding down doesn't potentially cause as many headaches. I showed rounding up, because that's what Dandas seemed to explicitly want to illustrate.

I've assumed that the date is accounted for in another field - but, if it comes into play at all - you're right, it would have to be manipulated in the case of a midnight change.

I'm also aware that I couldn't find Steve's midnight handler, but I don't think that my formula increases to 24 - hence the &quot;hour < 23&quot; handler.

Naith

 

[SmpTraining]

Jim, You are totally right - I stand corrected. Do I wish I'd tested my suggestion or what? Steve Phillips, Crystal Consultant

 

[Naith]

Before we get hung up on the whole date issue though, you can see that the field in question is a string containing only time from the initial post:

&quot; TrimRight ({@StartTime} [4 to 5])) < &quot;31&quot; &quot;

...bearing in mind that you can only TrimRight on a text string, and if the minute position is 4 to 5, it can only contain a time.

Naith

 

[Ngolem]

This was one of my questions in the first place....Why are manipulating a string? When it is easier to manipulate the time itself.

Our original poster has abandoned us. I see no reason for the string...unless it is used elsewhere for something...if the value is a time value my formual is easily modified.

Steve - no problem...I had to test it myself before I commented...It isn't intuitive .... until after you've tested it Jim Broadbent