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}
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