Help in formula to calculate next record

[Coyote2]

Hi,
here is a sample data

ID Total Completed
----- --------- ----------
1 367 0
2 364 12
3 346 2
4 341 6
5 331 6
6 324 5
7 319 2
8 317 3
9 313 28
10 278 28
11 241 7


I need to create a formula (@actual) that skips the first record then subtract NEXT Record completed from total.
example:
the first record is 367
second record is 367 - 12 = 355
third record is 355 - 2 = 352
etc... see results below...


ID Total Comp Acutal
--- ------ ------- --------
1 367 0 367
2 364 12 355
3 346 2 353
4 341 6 347
5 331 6 341
6 324 5 336
7 319 2 334



I started this formula but it's not working:

local NumberVar x;
x:= {command.Running_Total};
if RecordNumber = 1 then x
else x := x - next({command.COMPLETED_COUNT})

am I close to the correct formula?

thank you for your help

 

[fisheromacse]

i do not have crystal in front of me to check my syntax, but i think this minor modification will do what you ask.

local NumberVar x;
IF onfirstrecord then x:= {command.Running_Total};
if RecordNumber > 1 then
x := x - next({command.COMPLETED_COUNT})
else x := x;
x

 

[Coyote2]

fisheromacse,
thanks for the hlep. the results are not correct. here is what I'm getting

ID Total Comp Acutal
------ ------- -------- ------
1 367 0 367 2 364 12 -2
3 346 2 -6 4 341 6 -6 5 331 6 -5 6 324 5 -2
7 319 2

my actual column should read
367
355 (367 - 12)
353 (355 - 2)
347 (353 - 6)
341 (347 - 6)
etc...

we are close..
thanks again.
336
334
331

 

[fisheromacse]

sorry for the delay in replying, busy busy busy days.

try removing the 'next' and see how it looks.

 

[Coyote2]

I did and still it's not working.
I really need to subtract the completed from the result of the previous total field.
the problem is that it's subtracting from Total field where I have to subtract from the result instead.

thank you.

 

[fisheromacse]

please copy paste the contents of your formula here again.
I just tested in my crystal with dummy data and it seemed to work(unless i am really missing something, which does happen)

here is what i have in my details:

### Formula Result
91 91 (91)
82 9 (91-82)
62 -53 (9-62)
49 -102 (-53-49)
29 -131 (-102-29)
26 -157 (-131-26)


Here are comments explaining what my thinking is. please feel free to correct anything that is not what you need/intend.

local NumberVar x;
//declare number variable

IF onfirstrecord then x:= {command.Running_Total};
//if 1st record, set the variable to be equal to the Running_Total field

if RecordNumber > 1 then
x := x - ({command.COMPLETED_COUNT})
else x := x;
//if not first record the variable is set to equal the previous variable value minus the amount in the current Completed_Count field, if the record number is equal to 1 then the value of the variable does not change from it's previous value (this should really never come into play as a record number check is in place earlier in the formula)

x
//display the value of the variable

 

[lbass]

Try:

whilerpintingrecords;
numbervar first;
numbervar comp := comp + {table.comp};
if onfirstrecord or
{table.groupfield} <> previous({table.groupfield}) then
first := {table.total};
first-comp

Add a reset formula to the group header (if there is one):
whileprintingrecords;
numbervar first := 0;
numbervar comp := 0;

-LB

 

[Coyote2]

Please see upladed image. it probably explain better than me.

thank you for your help

 

[lbass]

Whose formula did you try (in your upload)?

-LB

 

[Coyote2]

sorr lbass. I was referring to fisheromacse's formula. You replied during posting my upload so I didn't try your yet.

let me try it now and let you know.
thank you.

 

[Coyote2]

Thank you all for your help.
I used the below formula and worked like a charm.


IF OnFirstRecord THEN numberVar RunningTotal := {HYC_TASKS_BASELINE_COUNT_SP;1.Running_Total}
ELSE numberVar RunningTotal:= RunningTotal - {HYC_TASKS_BASELINE_COUNT_SP;1.COMPLETED_COUNT};