Calculating totals across a date range

[hpl2001]

Hello.

This is making my head hurt. Here's the scenario. I have a group of customers that stay in a certain area. Customer 1 might be there Jan 5-10. Customer 2 might stay Jan 1-7, customer 3 Jan 6-20 etc.

For my report, I need to iterate through all the days of January (although the date range will be a parameter and can be any amount of days), such that for each date in my date range I count the number of customers staying in the area on that date.

So output would look like this:

Jan 1 - 1
Jan 2 - 1
Jan 3 - 1
Jan 4 - 1
Jan 5 - 2
Jan 6 - 3

etc.

Any thoughts? I've been trying to use arrays but I'm running into problems around where the formula would go and how to display results etc.

TIA!!

Holly

 

[lbass]

You need to show us how the customer data appears in the database at the detail level. Is there a start date and end date for each customer? Or instead, a range? Are the dates of date datatype? Or are they strings? Etc. Maybe show some sample data per customer.

-LB

 

[hpl2001]

Ok. Let's say there are three fields in my table: Name, start date and end date. Given a report parameter of a date range, I need my report to display the number of people who are there of each day of the parameter range. Does that help?

 

[lbass]

Well, not quite—ARE there separate start and end date fields per customer? Are they date type fields? I’m asking how the data actually is entered into the database.

-LB

 

[lbass]

Actually, can you also please say whether you have a field that includes all calendar dates?

-LB

 

[hpl2001]

Let me try again. Table has three fields:

Name - text type
arrival date - date type
departure date - date type.

When ordering the report, the user enters two parameters, report start date (date type) and report end date (date type)

The output would look like this

Jan 1, 2019: 10
Jan 2, 2019: 0
Jan3, 2019: 2

Where number are a count of people who are in attendance on any given day. i.e. count where arrival date >= report start date or departure date <= report end date.

 

[hpl2001]

There are no fields that have the actual calendar date. That's the problem for me. How to iterate through consecutive dates that aren't actually in the database.

And thanks for your help.

 

[lbass]

Okay, one further question -- will the date range ever cross months, e.g., Jan 15 - March 15, etc.?

-LB

 

[lbass]

Create a new report, using any table that has at least as many records as the number of dates in potential date ranges, e.g., if the date range could be for a year, choose a table that has at least 366 records. Add a field that cannot be null to the report header and suppress it.

Create two date formulas {?Start} and {?End}.

Then create a formula {@dayofyear} and place it in the detail section:

whilereadingrecords;
numbervar i:=i+1;
datevar x;
x := {?Start}+i-1;
x

Next go to report->selection formula->record and enter:

{@dayofyear} in {?Start} to {?End}

Then insert a subreport in the detail section that uses your customer data table. Add Customer ID, arrival date and departure date to the detail section. Insert a distinctcount on customer ID in the subreport footer. Next go to the subreport linking screen and choose {@dayofyear} as the linking field (you might have to check "use subreport field" to get this to work--but without selecting any field). Then in the subreport, go to report->selection formula->record and enter:

{?Pm-@dayofyear} >= {table.arrivaldate} and
{?Pm-@dayofyear} <={table.departuredate}

Suppress all sections of the subreport except the report footer and then in the main report, remove the subreport borders (format->subreport->borders and change "single" to "none".

-LB

 

[hpl2001]

Thank you for this. Gives me something to chew on.

H.

 

[hpl2001]

Works a charm. Thanks again.

H.