crystal reports compare 2 date ranges in same report 1 year apart

You could use a subreport for the previous year but I think it could be done with a single report.

The issue with the subreport would be how to ensure the rows line up.

To avoid the duplicate prompt link the subreport on the date range or on formulas with the year corrected.

mlmcc

This may seem redundant but I ended up creating two separate duplicate data sources, then created date parameters for the current year and the prior year.  I was able to link the two data sources together via the Client PKs.  I designed the report to look at the latest client record for each of the date parameters by placing the detail into the group by section and sort descending.

You could subtract 1 year from the starting and ending dates to get the dates for the previous year.  The only potential problem is leap years.  For example, if your ending date was 02/28/2017 and you subtracted 1 year from that, you'd get 02/28/2016, which is correct, but if you're working with months, you might want the ending date for the prior year to be 02/29/2016 instead.

 Assuming that that's not a problem ...

 You could have the record selection include the records between the entered start and end dates, and the adjusted dates for last year.  Something like:

({date field} in {?StartDate} to {?EndDate} or
{date field} in DateAdd ("yyyy", -1, {?StartDate}) to DateAdd ("yyyy", -1, {?EndDate})) and
 <and other conditions you may have>

 So now the report includes the records from this year and from last year.

 You'd have four separate formulas, for the "Lead Source" and "Sales TTL" for this year and last year, and have each pair of formulas check the date field.  For example:

// Current year Lead Source
if {date field} in {?StartDate} to {?EndDate} then
  <your Lead Source formula>

// Previous year Lead Source
if {date field} in DateAdd ("yyyy", -1, {?StartDate}) to DateAdd ("yyyy", -1, {?EndDate}) then
  <your Lead Source formula>

 Then you could group on the account or whatever you're reporting the sales for, put those 4 formulas in the detail section, create a summary on each formula for the group, and suppress the details.  That should let you get the figures for this year and last year on one line.  You can put the summaries in the group header if you like.

 James

Thanks everyone - I'll be trying this Monday.

James,

Your select record formula did work to populate the data from current and past year, but the current year and past year formulas are retrieving both years instead of separating the data by current and past years. Here is the formula I have for the current year:

// current year lead source
if Date ({elms_leads.date_modified}) in {?FromDate} to {?ToDate} then
GroupName ({elms_leads_source.source_name}) + " " & Count ({elms_leads.id}, {elms_leads_source.source_name})

Do you see anything wrong with this?

Upon further discovery, the current and past formulas are somehow splitting the source between year ranges as shown in the attached image. The BOLD text on the left is the report data from both years, while the normal text on the right shows the results of the "Current year formula", and next to that the "Past year formula".
LeadSource.JPG

The problem with the formula that you posted is that Count will just give you the count for the entire group.  It won't (can't) separate them by year.  So, you're saying "if the date is between FromDate and ToDate, show me the count for the group" (not just for that year).

 If you want the count for each year, you can use a pair of formulas like these:

// count for this year
if Date ({elms_leads.date_modified}) in {?FromDate} to {?ToDate} then
  1
else
  0

// count for last year
if Date ({elms_leads.date_modified}) in DateAdd ("yyyy", -1, {?FromDate}) to DateAdd ("yyyy", -1, {?ToDate}) then
  1
else
  0

 A summary (not count) on each of those formulas will give you the count for each year.

 Then the formula that you posted would be:

// current year lead source
if Date ({elms_leads.date_modified}) in {?FromDate} to {?ToDate} then
GroupName ({elms_leads_source.source_name}) + " " & Sum ({@count for this year}, {elms_leads_source.source_name})


 James

James,

These changes worked for separating the data - awesome!  

I still have the issue where the data is alternating between years instead of filling every row. In the attached image the black bold text is the two years combined, the green text is current year, and the blue text is the past year. The green and blue columns should not have the empty rows - they seem to alternate back and forth.
LeadSource1.JPG

You're amazing James! That did it - works perfectly. Thanks very much.

James gives very clear and intuitive instructions - very knowledgeable!

You're welcome.  Glad I could help.

 James