Date Range Formula - Data from two months ago only

[socalvelo]

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

 

[pmax9999]

Hi socalvelo

Try this:

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

Hope this helps.

Cheers,
Pete

 

[socalvelo]

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})

 

[fisheromacs]

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:

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

 

[pmax9999]

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:

{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