Need to compare a record 2 records back

[Dandas]

Hi.

I need to compare a cuurent record to a record 2 records back. Obviously I can get 1 back by using the "previous" command but I don't know how to go further.

I tried to create a calculated field @previous1 and then take a previous of that field by creating @previous2 but it did not work.

Any suggestions would be greatly appreciated.

Thanks

 

[dgillz]

You are going to have to use variables to do this. Create 2 formulas, OddPrevious and EvenPrevious. Start your formula like this:

@EvenPrevious
WhilePrintintgRecords;
If Remainder(RecordNumber,2)=0 then Numbervar EvenPrevious:={YourFieldName};

The above formula will store that field value until you address it later, in a formula like the following:

@Compare2RecordsPriorEven
WhileReadingRecords;
Numbervar 2RecordsPriorEven;
if Remainder(RecordNumber,2)=0 then Numbervar 2RecordsPriorEven:={YourFieldName}-NumberVar EvenPrevious;

I used a subtraction of the 2 fields in my example but you should be able to use any comparison. Please note that the first formula is WhilePrintingRecords; while the second is WhileReadingRecords.

I have not tested this, nor have I ever done anything similar to this in 6 years of writing crystal reports, but I believe this will work.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[smatthews]

One To Many Relationship with Conditional Printing
I have two tables. Table_1 has one record for every person. Table_2 could have one to six records or no records at all for a person in Table_1. The key is an ID field and a TYPE field. If a person has a Type_1 record I want to print out the data in that record. If the person has no Type_1 record then I want to print out a blurb stating "we do not have this info on file." I want to do this for all six Types. Any help will be greatly appreciated. Thanks.

 

[smatthews]

Sorry...not a reply...a question entered in the wrong box.

 

[Dandas]

Hey Dgillz thanks for your response (and sorry it has taken me so long to get back to this - my boss threw a new piece of work at me).

I am totally unfamamilliar with using variables so I just copied your text and pasted it in, and changed the {YourFieldName} to my field name which is {VW_EVT_AGENT_ACTIVITY.AA_TYPE}.

When I click to check the formula for errors a message comes up that says "The remaining text does not appear to be part of the formula" and the cursor moves to just under "WhilePrintingRecords" (Where the bolded X is).

WhilePrintingRecords
X
If Remainder(RecordNumber,2)=0 then Numbervar EvenPrevious:={VW_EVT_AGENT_ACTIVITY.AA_TYPE}


I tried retyping your formula from scratch just incase it did not like the format copied over but still did not work.

Any suggestions on how to get it to work would be appreciated.

Thanks heaps.

 

[Naith]

You seem to have neglected the semicolon required after the WhilePrintingRecords function.

Naith

 

[Dandas]

The simple things are usually the ones that get me !!

Thanks for that.

Now I am having trouble with the second part. I have copied Dgillz formula straight over and replaced it with my field names and I get an error message saying "a variable name is expected here" and the cursor moves to where the bolded "X" is.

WhileReadingRecords;

Numbervar "X" 2RecordsPriorEven;
if Remainder(RecordNumber,2)=0 then
Numbervar 2RecordsPriorEven:=VW_EVT_AGENT_ACTIVITY.AA_TYPE}-
NumberVar EvenPrevious;

Sorry about what seem might like simple questions to you guys, but I only have pretty basic knowlege of this stuff.

Thanks heaps

 

[synapsevampire]

Don't use an X:

WhileReadingRecords;
Numbervar 2RecordsPriorEven;
if Remainder(RecordNumber,2)=0 then
Numbervar 2RecordsPriorEven:=VW_EVT_AGENT_ACTIVITY.AA_TYPE}-
NumberVar EvenPrevious;

I didn't analyze the content, but the above should at least be error free.

-k

 

[Dandas]

LOL, sorry I confused you.

I put the "X" there to show you where the cursor stopped when I got the error message. It is saying that where the X is, the system is expecting a variable name.

Hope this makes a little more sense.

Thanks

 

[synapsevampire]

Get rid of the whilereadingrecords, use whileprintingrecords.

WhilePrintingRecords;
Numbervar 2RecordsPriorEven;
if Remainder(RecordNumber,2)=0 then
2RecordsPriorEven:=VW_EVT_AGENT_ACTIVITY.AA_TYPE}-
EvenPrevious;
2RecordsPriorEven

-k

 

[lbass]

I think the formula is erroring because of the "2" in "2RecordsPriorEven." Change it to "two" and then it should work. But...when I tested the formulas, the first formula returned a value for every even record. The second formula subtracted the same field from itself for even records, yielding zeros for all records. I don't think the formulas are forcing the evaluation of previous records. Wasn't sure how to fix that and get a "previous" value for numbervar EvenPrevious.

You could just use the next and previous functions together, e.g.,

next({table.field})-previous({table.field})//substitute your operator

...which would give you the value you are seeking--just in the wrong row, like this:

a
b a-c
c d-b
d e-d
e

I'm not sure if there's a way to align the comparison with the "next" field though.

-LB

 

[Dandas]

Only thig is I need to look at a) while I am at c). In the below example I need to be able to write a formula that says "if a is "unavailable" or "available" (there are numeric codes for each of these) then display that state next to c)"

a) Available
b) Unavailable
c) AfterCallWork

So what I need returned is

c) AfterCallWork - Available

And then I will have another formula measuring the time between the two. But like I said my problem is being able to check a) from c).

Thanks heaps for your suggestions guys.... Keep it up.

 

[lbass]

I think you need to tell us field names and a little about table structure. Your last post makes it sound like "Available" and "Unavailable" are instances of the same field, while "AfterCallWork" might be from a different field. Is that the case? How about showing us some sample rows and giving us an idea of what you're trying to achieve?

-LB

 

[Dandas]

Ok. This is the run down.

I do the reporting and statistics for a call centre and the agents are measured on things such as the time spent working on calls, and after calls.

We are using a Rockwell switch and it is very smart and records every single thing that happens, including touchpad keystrokes and the amount of time spent in particular states like on a call, or in available, or after call work, or on a secondary call etc (these states are controled by buttons on the phone eg. to go into unavailable you press the unavailable button).

In call centre's there is a massive need to moniotor and minimise the amount of time spent in AfterCallWork, so the agents are monitored real time by a guy who watches a screen to see that they don't spend more than 5 mins in AfterCallWork. If the agents go over 5 mins he calls the team leader.

The agents caught on to this so started pressing the unavailable button just before 5 mins in AfterCallWork and then went back to AfterCallWork to avaoid the 5 min threshold.

So to find the people doing this my collegue and I made a report that says "Only display the record if this record is unavailable or available, and the previous record is AfterCallWork and the next record is also AfterCallWork" and then I sum the time.

It looks like this - (3 and 4 are unavailable and available and 5 is AfterCallWork)

@CallWork1
//This is the previous state

if {VW_EVT_AGENT_ACTIVITY.AA_TYPE} in [3,4] and
Previous ({VW_EVT_AGENT_ACTIVITY.AA_TYPE}) IN [5] and
NEXT ({VW_EVT_AGENT_ACTIVITY.AA_TYPE}) IN [5]then

totext (Previous({VW_EVT_AGENT_ACTIVITY.AA_ACTIVITY})+(" &quot+RISecondsToHMS(Previous({VW_EVT_AGENT_ACTIVITY.AA_CALL_DURATION})))
else
""

Then we measure the amount of time with this

@ActivityDuration
if {VW_EVT_AGENT_ACTIVITY.AA_TYPE} in [3,4] and
Previous ({VW_EVT_AGENT_ACTIVITY.AA_TYPE}) IN [5] and
NEXT ({VW_EVT_AGENT_ACTIVITY.AA_TYPE}) IN [5]then

totext ({VW_EVT_AGENT_ACTIVITY.AA_ACTIVITY})+(" &quot+RISecondsToHMS({VW_EVT_AGENT_ACTIVITY.AA_CALL_DURATION})
else
""
Then we check that they went back into AfterCallWork

@CallWork2

//This is the current state
if {VW_EVT_AGENT_ACTIVITY.AA_TYPE} in [3,4] and
Previous ({VW_EVT_AGENT_ACTIVITY.AA_TYPE}) IN [5] and
NEXT ({VW_EVT_AGENT_ACTIVITY.AA_TYPE}) IN [5]then

totext (Next({VW_EVT_AGENT_ACTIVITY.AA_ACTIVITY})+(" &quot+RISecondsToHMS(Next({VW_EVT_AGENT_ACTIVITY.AA_CALL_DURATION})))
else
""

Then total the combined time that should have been recorded as actually spent in AfterCallWork

WhilePrintingRecords;

if {VW_EVT_AGENT_ACTIVITY.AA_TYPE} in [3,4] and

Previous ({VW_EVT_AGENT_ACTIVITY.AA_TYPE}) IN [5] and
NEXT ({VW_EVT_AGENT_ACTIVITY.AA_TYPE}) IN [5]
Then
RISecondsToHMS (next({VW_EVT_AGENT_ACTIVITY.AA_CALL_DURATION})+previous({VW_EVT_AGENT_ACTIVITY.AA_CALL_DURATION}))
else
""

They caught on to how we are capturing this as well and started switching from AfterCallWork to available to unavailable back to AfterCallWork which is why I need to compare two records back, because as itis now it looks backwards 1 record for an AfterCallWork when it was 2 back.

Does this make sense? Sorry about the length of this post but I figured it may make more sense if you understand why I am trying to do it.

Thanks again

 

[Dandas]

Anyone come up with any ideas the last few days?

 

[lbass]

I actually spent a lot of time thinking about this and fooling around with a mock report. I was thinking maybe you'd have better luck if you evaluated the sequence of activities by collecting the activity types in a variable and then using running totals that are reset based on the presence of certain values in the string variable. This is as far as I got.

Group on {table.emplID}.

Create a formula {@pattern} and place in details:

whileprintingrecords;
stringvar pattern := pattern+totext({VW_EVT_AGENT_ACTIVITY.AA_TYPE},0,""
pattern;

Create a formula {@resetgrouppattern} and place in group header:

whileprintingrecords;
stringvar pattern := "";

Create a second reset {@resetdetailpattern} and place in detail section:

whileprintingrecords;
if instr({@pattern},"1&quot > 0 or
instr(mid({@pattern},2),"5&quot > 0
then stringvar pattern := "";

This assumes a reset if the employee is on a call (I assumed a code of 1 for this) or after a second instance of
AfterCallWork.

This should give you results that look like this for @pattern:

type pattern duration (see below)
1 1 0
5 5 20
1 51 0
5 5 15
3 53 35
4 534 50
5 5345 60
3 3 0
1 31 0
5 5 5

Then create a formula for call duration for the sequence starting and ending with a "5" by creating a running total with conditions, something like {@duration}:

whileprintingrecords;
numbervar duration;
if {VW_EVT_AGENT_ACTIVITY.AA_TYPE} = 1 or
left({@pattern},1) <> &quot;5&quot;
then duration := 0 else
duration := duration + {VW_EVT_AGENT_ACTIVITY.AA_CALL_DURATION};

Create a reset {@resetduration} and place in group header:

whileprintingrecords;
numbervar duration := 0;

See the chart above for how duration should look.

I'm not sure this will meet your needs, but maybe it'll trigger some ideas at least.

-LB