Dynamically group records by date

[Former Member]

I am attempting to create a report that will dynamically group records into a set number of date buckets. This is similar to grouping records by a date field and setting the days, weeks, months, etc property but instead of grouping by a set time span I want to a specific number of date groups regardless of date span. So say i have records where the first date is today at 1am and the last record is today at 9 pm. I want the data grouped into 10 groups and the time calculated for that group based on total time span / 10. The first group would be 1AM to 3AM, the second would group 3AM to 5AM, etc.. The reason I am doing this is for a chart that displays record counts over time but the overall timespan will never be known until runtime. Setting the chart for hourly or weekly doesn't work because if the user runs the report over a year the dates will be illegible.

Thanks in advance!

[Former Member · Accepted solution]

Well this SHOULD be easy. But leave it to CR make not...

You can start by finding the minimum & maximum dates within your range:

Local DateTimeVar MinDate;
MinDate := Minimum({Table.DateField})

and

Local DateTimeVar MaxDate;
MaxDate := Maximum({Person.ModifiedDate})

Then figure out what the the interval would be if the span is broken down into 10 equal parts"

DateDiff("n", {@MinDate}, {@MaxDate}) / 10

From there just use a formula to segregate each records into the appropriate groups:

EvaluateAfter({@Interval});

IF {Table.DateField} >= {@MinDate} 
    AND {Table.DateField} <= DateAdd("n",{@Interval}, {@MinDate}) THEN 1 ELSE

IF {Table.DateField} > DateAdd("n",{@Interval}, {@MinDate})
    AND {Table.DateField} <= DateAdd("n",{@Interval} * 2, {@MinDate}) THEN 2 ELSE

IF {Table.DateField} > DateAdd("n",{@Interval} * 2, {@MinDate})
    AND {Table.DateField} <= DateAdd("n",{@Interval} * 3, {@MinDate}) THEN 3 ELSE

IF{Table.DateField} > DateAdd("n",{@Interval} * 3, {@MinDate})
    AND{Table.DateField} <= DateAdd("n",{@Interval} * 4, {@MinDate}) THEN 4 ELSE

IF {Table.DateField} > DateAdd("n",{@Interval} * 4, {@MinDate})
    AND {Table.DateField} <= DateAdd("n",{@Interval} * 5, {@MinDate}) THEN 5 ELSE

IF {Table.DateField} > DateAdd("n",{@Interval} * 5, {@MinDate})
    AND {Table.DateField} <= DateAdd("n",{@Interval} * 6, {@MinDate}) THEN 6 ELSE

IF {Table.DateField} > DateAdd("n",{@Interval} * 6, {@MinDate})
    AND {Table.DateField} <= DateAdd("n",{@Interval} * 7, {@MinDate}) THEN 7 ELSE

IF {Table.DateField} > DateAdd("n",{@Interval} * 7, {@MinDate})
    AND{Table.DateField} <= DateAdd("n",{@Interval} * 8, {@MinDate}) THEN 8 ELSE

IF {Table.DateField} > DateAdd("n",{@Interval} * 8, {@MinDate})
    AND {Table.DateField} <= DateAdd("n",{@Interval} * 9, {@MinDate}) THEN 9 ELSE

IF {Table.DateField} > DateAdd("n",{@Interval} * 9, {@MinDate})
    AND {Table.DateField}  <= {@MaxDate} THEN 10

This is where CR drops the ball... IMHO... it WON'T allow you to to group by a formula field that uses an aggregate in the formula (in this case Minimum & Maximum)... It will however allow to to graph on it, which I assume is what you are actually trying to do. If anyone knows a way to work around the grouping issue, I'd love to know it myself.

HTH,

Jason

[Former Member · Accepted solution]

Now that I think about it...

If you use separate Begin Date & End Date parameters, you could do away with the MinDate & MaxDate formulas, using the parameters in their place. This would get rid of the aggregates in the formula...