Sum of a formula field

[Joy1]

Please assist
CR9 connecting to Oracle DB via ODBC

SAPLE DATA:

Ref Open Xfer Close From To Time@NOC
I801
I801 02Aug 01Sep 18Oct INLAND NOC1 452
I801 02Aug 05Sep 18Oct NOC1 UNIX 0
I801 02Aug 18Sep 18Oct UNIX NOC2 673
I801 02Aug 23Sep 18Oct NOC2 BASC 0
I801 02Aug 30Sep 18Oct BASC NOC3 350
I802
I802 05Aug 06Sep 28Oct UNIX NOC3 254
I802 05Aug 17Sep 28Oct NOC3 INLAND 0
I802 05Aug 10Oct 28Oct INLAND BASC 0
I802 05Aug 18Oct 28Oct BASC NOC1 71
I802 05Aug 22Oct 28Oct NOC1 NOC3 25
Data is group per Ref.
Time@NOC calculates the datediff between the time the ref spent from one NOC group to the other (in minutes).
Time@NOC = {table.xfer_date} - next({table.xfer_date})

I would like to calculate the sum(Time@NOC) per group.

Any ideas?




Joy

 

[lbass]

What is your actual formula? It looks like you are not taking into account that the next xfer might relate to a different group, so you probably need to add something like:

if {table.ref} = next({table.ref}) then //the rest of your formula

To sum the differences within each group, create three formulas:

//{@reset} to be placed in the ref group header:
whileprintingrecords;
numbervar sumdiff;
if not inrepeatedgroupheader then
sumdiff := 0;

//{@accum} to be placed in the detail section:
whileprintingrecords;
numbervar sumdiff := sumdiff + {@yourformula};

//{@display} to be placed in the group footer:
whileprintingrecords;
numbervar sumdiff;

-LB

 

[buck149]

First, create group by Ref then insert your Time@NOC into detail section then right click on the formula, selection sum. you are done.

 

[dgillz]

You cannot use a simple insert summary like buck149 stated if you are using a formula withe the next() or previous() functions, you need to use the 3 formula approach like lbass indicated.

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

http://www.gainfocus.biz

"A fine is a tax for doing wrong. A tax is a fine for doing well.
" - unknown

 

[Joy1]

Thanks guys

buck149: i did try that b4 posting this thread & it didn't work - but thanks for trying.

lbass: your formulas work like a charm - thankx a mill.
PS: yes the formula has an if statement b4 the calculation. 4 reference here's the actual formula:

If {table.To} like "NOC*"
and {table.ref} = next ({table.ref}) then
DateDiff ("n", {table.xfer_date}, next({table.xfer_date}))

dgillz: thanks for the pointers as well

Joy

 

[lbass]

Then I guess your sample data is wrong, as there should be zeros in the final row for each group, since {table.ref} would not = next({table.ref}) for that row.

-LB

 

[Joy1]

Oh sorry my mistake - the formula actually reads out like this:

If {table.To} like "NOC*"
and {table.ref} = next ({table.ref}) then
DateDiff ("n", {table.xfer_date}, next({table.xfer_date}))
Else
If {@Xfer To} like "NOC*"
and {table.ref} <> next ({table.ref}) then
DateDiff ("n", {table.xfer_date}, {table.close_date})

Joy

 

[lbass]

OK, that makes sense.

-LB