Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: Grouping totals by consecutive dates Post Reply Post New Topic
Author Message
oscope
Newbie
Newbie


Joined: 15 Sep 2015
Location: United States
Online Status: Offline
Posts: 1
Quote oscope Replybullet Topic: Grouping totals by consecutive dates
    Posted: 08 Jan 2024 at 6:13am
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

IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.020 seconds.