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

Gary Demos
Gary Demos used Ask the Experts™
on
In a sales report I want to enter a date range from two DB fields used in a formula and then list two columns of results that list the results from the date range entered, as well as a comparison column of data from exactly one year previous. I'd like them to be listed side by side. Would I use a Subreport for the comparison data? And if so, how would I take the entered date range and have the Subreport use data from the previous year? I want only one prompt for a date range and have the report handle pulling data from the previous year for the comparison.

For example:

LEAD SOURCE CURRENT DATE RANGE:                                               LEAD SOURCE ONE YEAR AGO:
Lead Source: (formula 1)       Sales TTL: (formula 2)                          Lead Source: (formula 1)       Sales TTL: (formula 2)

I hope this is clear enough to follow, and thanks for the help!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
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
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Gary DemosPresident

Author

Commented:
Thanks everyone - I'll be trying this Monday.
Gary DemosPresident

Author

Commented:
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?
Gary DemosPresident

Author

Commented:
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
Gary DemosPresident

Author

Commented:
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
This is just a guess (since I haven't seen your report), but if the green and blue formulas are like the "current year lead source" formula that you posted yesterday, then you need to remove the date check.  I'm guessing that those formulas are in a group header or footer.  When those formulas check the date, they only see the date in the first (or last for the footer) record in the group, so only one of the date checks will be true.  IOW, depending on the date in the first (or last) record in the group, you'll get either the figure for this year or for last year, but you won't get both.

 Change the current year formula to just:

// current year lead source
GroupName ({elms_leads_source.source_name}) + " " & Sum ({@count for this year}, {elms_leads_source.source_name})

 And do the same kind of thing for last year.  Just remove the date check, and do a summary on the count formula for last year.

 James
Gary DemosPresident

Author

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

Author

Commented:
James gives very clear and intuitive instructions - very knowledgeable!
You're welcome.  Glad I could help.

 James

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial