×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Calculating totals across a date range

Calculating totals across a date range

Calculating totals across a date range

(OP)
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

RE: Calculating totals across a date range

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

RE: Calculating totals across a date range

(OP)
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?

RE: Calculating totals across a date range

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

RE: Calculating totals across a date range

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

-LB

RE: Calculating totals across a date range

(OP)
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.

RE: Calculating totals across a date range

(OP)
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.

RE: Calculating totals across a date range

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

-LB

RE: Calculating totals across a date range

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

RE: Calculating totals across a date range

(OP)
Thank you for this. Gives me something to chew on.

H.

RE: Calculating totals across a date range

(OP)
Works a charm. Thanks again.

H.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close