I have a report that I would like to sum on consecutive dates and show that total, then restart the sum and begin again within the same MANum group .

The report looks like this

GH #1 County

GH #2 Service code

GH #3 MANum unique Id contains the reset formula ttlunits:= 0

Details fields: Number of Units, rate, begin date and the below formula

GF #3 MANum contains sum of all units and the sum of consecutive units

GF #2 Service Code

GF #1 County

example output:

7/1/2023 10 units

7/2/2023 5 units

group of 15 units for date range for same MANum

gap

7/5/2023 5 units

7/6/2023 3 units

Group of 8 units for date range for same MANum

I use this formula in the details section:

whileprintingrecords;

numbervar ttlunits;

if onfirstrecord

then (

ttlunits:= ttlunits+{Billings.Number of Units}

)

else

if not onlastrecord and

dayofweek({Billings.Begin Date}, crSunday) in [1 to 7] and

{Billings.Begin Date} = previous({Billings.Begin Date}) +1 and

{Billings.Begin Date} = next({Billings.Begin Date}) - 1

then (

ttlunits:= ttlunits+{Billings.Number of Units}

)

else

if not onlastrecord and

dayofweek({Billings.Begin Date}, crSunday)=1 and

{Billings.Begin Date} = previous({Billings.Begin Date}) +3 and

{Billings.Begin Date} = next({Billings.Begin Date}) - 1

then (

ttlunits:= ttlunits+{Billings.Number of Units}

)

else

if not onlastrecord and

dayofweek({Billings.Begin Date}, crSunday) in [1 to 7] and

{Billings.Begin Date} = previous({Billings.Begin Date}) + 1 and

{Billings.Begin Date}<> next({Billings.Begin Date}) - 1

then (

ttlunits:= ttlunits+{Billings.Number of Units}

)

else

if not onlastrecord and

dayofweek({Billings.Begin Date}, crSunday) in [1 to 7] and

{Billings.Begin Date}<> previous({Billings.Begin Date}) + 1 and

{Billings.Begin Date} = next({Billings.Begin Date}) - 1

then (

ttlunits:= ttlunits+{Billings.Number of Units}

)

else

if {Billings.Begin Date} <> next({Billings.Begin Date}) - 1

then (

ttlunits:= 0;

)

else

if onlastrecord

then (

ttlunits:= ttlunits+{Billings.Number of Units}

)

else

ttlunits:= {Billings.Number of Units}

For the most part this formula works but there are some anomolies IE:

Units date formula calculation

24 7/1/2023 24

25 7/2/2023 49

gap

24 7/5/2023 73

24 7/6/2023 97 should be 48

Any help would be greatly appreciated and thank you in advance.

Steve