×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

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

Date Range Formula - Data from two months ago only

thread767-1820640

Date Range Formula - Data from two months ago only

Date Range Formula - Data from two months ago only

(OP)
CR 2016
SQL Server

I'm writing a report will I will have two columns of summary line items of the last two complete months

I'm using lastfullmonth for "last month" or I can also use this:
{date_field} in datevalue(year(currentdate),1,month(currentdate))
to currentdate-day(currentdate)

What I am having trouble with is adjusting the above formula for the complete month two months ago.

I can easily accomplish this by using a crosstab, but I need to have the formulas in a group header, so I'm making a manual crosstab and need the proper formula for dates two months ago.
Any help is appreciated.

Thanks
Ed

RE: Date Range Formula - Data from two months ago only

Hi socalvelo

Try this:

CODE

{Table.Date} in [DateSerial(Year(Today()), Month(Today())-2, 1) to DateSerial(Year(Today()), Month(Today())-1, 1) -1]

Hope this helps.

Cheers,
Pete

RE: Date Range Formula - Data from two months ago only

(OP)
Pete, thanks for helping me out again. The formula works and gives me the last two full months when I put it in the Report Selection Editor with one change to get two full months of data.

When I incorporate this into a formula to get an try to get a distinct count of records for the full month (two months ago) I get records for both months in the range.

Here's what I put in the report selection editor using your formula as a model:
{vw_report_event_infos.report_created_date} in [DateSerial(Year(Today()), Month(Today())-2, 1)
to currentdate-day(currentdate)]

I set up this formula to get distinct count for records for the full month of January (being that today is March 1st)
if{vw_report_event_infos.report_created_date} in [DateSerial(Year(Today()), Month(Today())-2, 1)
to DateSerial(Year(Today()), Month(Today())-1, 1) -1]
then distinctcount({vw_report_event_infos.report_id},{vw_report_event_infos.report_type})

RE: Date Range Formula - Data from two months ago only

Your formula does a distinct count for all available records, just like it is being asked to do.
the if....then part does not change the record set.

I would use an 'accumulation'/'manual running total' formula like below:

CODE --> 

numbervar count2;
if{vw_report_event_infos.report_created_date} in [DateSerial(Year(Today()), Month(Today())-2, 1)
to DateSerial(Year(Today()), Month(Today())-1, 1) -1]
then count2 := count2 + 1 
else count2 := count2;
count2

RE: Date Range Formula - Data from two months ago only

In Crystal there is nearly always multiple ways to achieve the same thing. One of the simplest ways would be to add a group (remembering to change the default of "for each week" to "for each month") on the {vw_report_event_infos.report_created_date} and then add a Distinct Count summary of {vw_report_event_infos.report_id}.

If the additional grouping is not an option for some reason, a Running Total as follows should also work:

Field to Summarize: {vw_report_event_infos.report_id}
Type of Summary: Distinct Count
Evaluate: Use a Formula:

CODE

{vw_report_event_infos.report_created_date} in [DateSerial(Year(Today()), Month(Today())-2, 1)
to DateSerial(Year(Today()), Month(Today())-1, 1) -1]
Reset: Never

I often use manual Running Totals as suggested by fisheromacs. The challenge here is that it will count every record, so if there are duplicate entries (as suggested by the fact you are using a "Distinct Count" rather than a "Count") the result will be inflated. This can certainly be addressed in the code, but I feel that the standard Running Total as above might be simpler.

Hope this helps

Cheers,
Pete

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