Monthly Total Days in a Date Span 1

[dazum]

I am using CR XI
I would like to find the simplest way to get a monthly total days within a year's span
For Example
I would like to get the total number of days in January 2014 between the Begin Date and End Date
Begin Date End Date
12-9-13 -- 7-22-14 (need to show 31 days for this example)
1-8-14 -- 8-15-14 (need to show 23 days for this example)
1-15-14 -- 1-20-14 (need to show 5 days for this example)
1-28-14 (End Date is null, need to show 3 days for this example)

I could write a formula for each month of the year, but is there an easier way using a Running Total?

 

[Charliy]

I'm assuming you have a parameter to tell it you want January (or whatever month)
If you're using a date range parameter, then create two formulasL
{@beginmonth} - minimum({?DateRange})
{@endmonth} - maximum({?DateRange})

Next I would create a formula {@edate} that is like:
if isnull({table.enddate})
then {@endmonth})
else if {table.enddate} > {@endmonth}
then {@endmonth}
else {table.enddate}

Do the same for {@bdate}
if isnull({table.begindate})
then {@beginmonth})
else if {table.begindate} < {@beginmonth}
then {@beginmonth}
else {table.begindate}

Now you can easily count the days in the selected month:
({@edate} - {@bdate})+1



then your days calculation is simply ({@edate} - {table.begindate})+1

 

[dazum]

Charily
I need to clarify more Of what I need to do.
I am getting a count of days in the 1st half of 2014, then get the total days for each of the 6 months of the year.
I.E. if the total days from 1-1-14 to 6-30-14 = 600 days, the monthly totals need to equal something like Jan - 150 days, Feb - 50 days, Mar - 200 days, Apr - 75 days, May - 50 days, June - 75 days.
I am using begin date and end date parameters to get the total days, but I not sure what is the best way to get the monthly total days.

 

[Charliy]

Are you grouping by month?

 

[dazum]

No I'm not grouping by months because I'm not sure which date to use to group it by. For example a Begin Date of 12-19-13 might be before the Begin Parameter 0f 1-1-14, and the End Date is 1-3-14. These dates would needed to be counted in the January Group, but the Begin Date is not in January, the End Date is in January.

 

[pmax9999]

In your example, do you just want to count only for January or would you want to count for all months. In other words, in the first row of your sample data do you want to see Dec (22), Jan (31), Feb (28), Mar (31), Apr (30), May (31), Jun (30), and Jul (22), or are you only interested in January?

Also, will the dataset span more than 12 months, ie could there be data for January 2014 and 2013, for example if the first record in your sample data was 12-9-12 to 7-22-14? If so, we would need to know the maximum number of years that could possibly be covered.

Assuming my understanding of the problem is accurate, you would need either one formula that calculates for each month, or alternatively a formula that uses 12 variables to calculate the days for each month.

And if the data could span more than 1 full year, you would need separate formulas (or variables) for each month of each year potentially appearing in the report. This means that if the data could potentially span 5 years there would need to be 60 (5 x 12) calculations.

Hope this helps.

If you require additional assistance, please confirm (or otherwise) my understanding and advise regarding the number of years that may be spanned by the data.

Cheers
Pete

 

[dazum]

Pete
I am going to need totals for each of the 12 months in the year. I was thinking along the same lines as you and am going to use 12 different formulas for totals of each month.
Thank You for your suggestion!

 

[pmax9999]

No problems Dazum, happy to help. If you need further assistance with the formulas, just let us know.

Cheers
Pete

 

[BettyJ]

Not sure if the following will work for you, but hoping that it will help you in some way.

If you have the ability to create tables in your database, create a dummy table with a field say [tt]dummy_dates[/tt] that contains all the dates in the year's span consecutively. (If not, you can create an excel spread sheet with a column named [tt]dummy_dates[/tt] that contains all the dates in the year's span consecutively)

create two date parameters
startdate
enddate

Place {dummytable.dummy_dates} in Details section

In the record selection, use
[tt]{dummytable.dummy_dates}>={?StartDate} and
{dummytable.dummy_dates}<={?EndDate}[/tt]

Insert Group
{dummytable.dummy_dates}
In Options
Under The section will be printed, select
for each month
Insert Summary
{dummytable.dummy_dates}
count
Group #1 - dummytable.dummy_dates-A

Suppress details.
This will work only if the dates are in a year's span (month should not be repeated)

eg: start date 12/1/2013
end date 11/30/2014