Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : 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
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 10 Jul 2024 at 6:49am
I realize this is a somewhat old post and I hope you were able to figure this out.

It looks like you're trying to get the daily and weekly sums of the number of units. If that's the case, here's what I would do:

1. Add two groups on the date field - the first one is at the weekly level and the other is at the daily level.
2. Suppress the details section and both group header sections
3. Create a sum of MANUM using the following as a template an put it in the Daily date group footer:

sum({table.Unitsfield}, {table.datefield}, "daily")

Put the date field and this formula in the daily date group footer.
4. Create a similar summary formula for the "weekly" date group footer that will contain the "group of XX units for date range for same MANum" text.

-Dell
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.031 seconds.