Date Range Formula - Data from two months ago only

Hi socalvelo

Try this:


Hope this helps.

Cheers,
Pete

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

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:

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:
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