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
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
-LB
RE: Calculating totals across a date range
RE: Calculating totals across a date range
-LB
RE: Calculating totals across a date range
-LB
RE: Calculating totals across a date range
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
And thanks for your help.
RE: Calculating totals across a date range
-LB
RE: Calculating totals across a date range
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
H.
RE: Calculating totals across a date range
H.