Formula based on the last record within a group 1

[Joy1]

Hi

Please assist if you can.
Below is the sample data of the report I'm working on:

RefNum Logged XferDate Closed XferFrom XferTo @NOC
I103
I103 05/09/06 05/09/06 22/12/06 FBS GX SAP
I103 05/09/06 05/09/06 22/12/06 GX SAP Connect
I103 05/09/06 08/09/06 22/12/06 Connect Witbank
I103 05/09/06 13/09/06 22/12/06 Witbank NOC Net ??
I103 05/09/06 19/10/06 22/12/06 NOC Net NOC Micro ??
I103 05/09/06 23/10/06 22/12/06 NOC Micro NT Applic
I103 05/09/06 23/10/06 22/12/06 NT Applic NOC Net ??
I103 05/09/06 24/10/06 22/12/06 NOC Net Business
I103 05/09/06 03/11/06 22/12/06 Business NOC Micro ??
I103 05/09/06 03/11/06 22/12/06 NOC Micro Citrix
I103 05/09/06 28/11/06 22/12/06 Citrix Wits

I104
I104 06/09/06 14/09/06 13/12/06 Niche Citrix
I104 06/09/06 29/10/06 13/12/06 Citrix ESM
I104 06/09/06 01/12/06 13/12/06 ESM NOC Micro ??

I'm trying to determine the following:
If the group to which the call was transfered (XferTo) is like NOC, then calculate the date difference from the day the call was transfered (XferDate) to the day the call was next transfered to the following group (next XferDate). The formula I've tried to use is:

If {@Xfer To} like "NOC*" then
DateDiff("n",{@Xfer Date},next({@Xfer Date}))

The formula works out fine until it comes to a point where the call was transfered to the group that is like NOC and at the same time closed by the very same group (e.g. as in I104). In this case, I tried extending the formula to include the Else statement:

If {@Xfer To} like "NOC*" then
DateDiff("n",{@Xfer Date},next({@Xfer Date}))
Else
If {@Xfer To} like "NOC*" and
{@Xfer Date} = Maximum ({@Xfer Date}) then
DateDiff("n",{@Xfer Date},{@Closed})

This formula doesn't give the correct answer if the group that closed the call was like NOC. Does anyone have any idea on how I could crack this formula so I can get the time that the call spent on the group that is like NOC.

NOTE: I've grouped my data according to the reference number(RefNum)

Any ideas are welcome. Thanks in advance.

Joy

 

[synapsevampire]

Rather than chatting about what doesn't work, and showing formulas we've no idea the contents of ({@Xfer To}), why not post the expected output and why?

Just build a mini requirement, and state technical information first, then explain things if you are compelled.

Including your software version is helpful too.

I gather that b[I103] and b[I104] are group values, but later you say:

"then calculate the date difference from the day the call was transfered (XferDate) to the day the call was next transfered to the following group (next XferDate). The formula I've tried to use is:"

So you say when it was transferedto the next group, when perhaps you mean to the next xfer_date.

Don't use terms for 2 different meanings, it's confusing.

You might want something like:

If {table.xferto} like "NOC*"
and {table.whateverthegroupfieldis} = next({table.whateverthegroupfieldis}) then
DateDiff("n",{XferDate},next({XferDate}))

-k

 

[Joy1]

Thanks K

Apologies for being vague in trying to explain myself. Let me try again:

Im working on CR9 and connecting to the Oracle database.
Requirement: to calculate how much time did the call spend within the NOC groups.

Yes I103 and I104 are my group values and yes I meant to calculate the difference from when the call was transfered to the NEXT transfer date.

Your formula works just fine - thanks. What if the NOC group was the last group to which the call was transfered(hence there won't be the next xferto date - like for instance in group I104)?

 

[synapsevampire]

What do you want to have happen?

Again, state your requirements.

As it stands, it shouldn't return anything.

-k

 

[Joy1]

In addition to the formula that you suggested, i want to still see values for if NOC was the last group to which the call was transfered. In such a case, i want to see the datediff between the transfer date and the close date.

I hope this makes sense.

 

[synapsevampire]

Try:

If {table.xferto} like "NOC*"
and {table.whateverthegroupfieldis} = next({table.whateverthegroupfieldis}) then
DateDiff("n",{XferDate},next({XferDate}))
else
If {table.xferto} like "NOC*"
and {table.whateverthegroupfieldis} <> next({table.whateverthegroupfieldis}) then
DateDiff("n",{XferDate},next({closedDate}))

-k

 

[Joy1]

Thanks K u r a *

It works - I only had to remove "next" in the last line of the formula coz in this case NOC will be the last record in the line of each respective group, thus there's no next close date for that particular group.

If {table.xferto} like "NOC*"
and {table.whateverthegroupfieldis} = next({table.whateverthegroupfieldis}) then
DateDiff("n",{XferDate},next({XferDate}))
else
If {table.xferto} like "NOC*"
and {table.whateverthegroupfieldis} <> next({table.whateverthegroupfieldis}) then
DateDiff("n",{XferDate},{closedDate})

THANKS - your help was much appreciated. And next time I'll stick to the facts and to the point .

 

[synapsevampire]

Ooops, forgot about the next when I changed the field name, glad you figured it out.

-k

 

[Joy1]

K

Do u think you can help me figure this one out:

The report works just fine until I come to the select expert. Currently the report is based on all records but now I want to filter only those to which the formula applied (i.e. the like "NOC*" groups only).

I figured I would use this in my select exptert:
{table.xferto} like "NOC*" OR
{table.xferto} startswith "NOC*"
works both ways.

PROBLEM:
[COLOR=red yellow]After the selection criteria has been applied[/color], the very last record does not print the result of the formula. I've noticed that it is the only line within the group but the likes of it display the formula results, the last record is the only exception.

Can you please help?



Joy