How do I have dates from 2010 and 2011 with the same day and month on the same row in Crystal Reports?

I am writing a Crystal Reports with transaction dates comparing activity for the same days in 2010 and 2011.  For example, 02/02/2011 with $5 and 02/02/2010 with $7.  I need to have these dates on the same row for each year:
             2011    2010  Difference
02/02          5         7            2
I need to make a calculation of the difference between 5 and 7 for comparative analysis.
garyjgsAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
LinInDenverConnect With a Mentor Commented:
This is being caused by Group 1, which I think is Command.Semana

If that is the same "Semana" as displayed on screen, the PDF shows:
Page 1, Semana = 29 for 7/25
Page 2, Semana = 30 for 7/25


Group 2 will only group things within Group 1... so if you can remove Group 1 of Semana that should fix it.
0
 
LinInDenverCommented:
i would start with a formula that just does MM/DD, and group by that. I think totext(datefield,'mm/dd') would work - but I don't have crystal on this machine to confirm...

next formula would be 2011 amounts
if year(datefield)=2011 then Amount else 0

next formula would be for 2010 amounts
if year(datefield)=2010 then Amount else 0

Insert these two formulas on your detail field, then right click and insert summary, sum.

create one more formula for the difference, which will be basically sum(2011amts,groupfield) - sum(2010amts,groupfield). place this in the group footer.

suppress your detail rows.
0
 
garyjgsAuthor Commented:
totext(datefield,'mm/dd') return 00 for the month/the correct value for the day is returned,
example, 00/17 for July 17
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
garyjgsAuthor Commented:
Changed the mm to MM and the totext worked.
0
 
LinInDenverCommented:
I knew it was something like that! It can be picky, can't it. :)
0
 
garyjgsAuthor Commented:
I do, however, still don't have amounts matching with month, day.
9-27-2011-4-50-52-PM.jpg
0
 
LinInDenverCommented:
Okay,

Insert a Group on the second column, and then insert sums on the 4th and 5th columns into the group footer.

Then create one more formula for the difference, which will be basically sum(2011amts,groupfield) - sum(2010amts,groupfield). place this in the group footer.
0
 
garyjgsAuthor Commented:
For example in the above screen print, I need to have one row for each day/month.
01/11 should have 1,090.94  and  1,177.13 in one row.
0
 
LinInDenverCommented:
yes, please follow my suggestion, that should do it.
0
 
garyjgsAuthor Commented:
What I find interesting is that the first row and last row of each group as illustrated in this resulting view have 0 in the first and last row of the group.
9-27-2011-5-39-16-PM.jpg
0
 
LinInDenverCommented:
Can you provide a screenshot of:

1) UnSuppress (or UnHide) your detail sections
2) Also showing the gray section names on the left hand side (i.e.: "Detail, Group Footer 1").

I just want to make sure your formulas are in the right spots.

Please also copy and paste your formula for %Difference into your reply.
0
 
garyjgsAuthor Commented:
0
 
garyjgsAuthor Commented:
0
 
garyjgsAuthor Commented:
Formula:

if Sum ({@DATE 2010}, {@MONTH-DAY}) = 0 then 0 else

( Sum ({@DATE 2010}, {@MONTH-DAY}) - Sum ({@DATE 2011}, {@MONTH-DAY}))/Sum ({@DATE 2010}, {@MONTH-DAY})

0
 
LinInDenverCommented:
Thanks - that all seems to be OK.

When you go to the PREVIEW tab, do you see 2 detail rows in groups  07/04 and 07/11?
0
 
garyjgsAuthor Commented:
Good question.  When reviewing the report Preview with details, there is on row for 07/11 on page 28 and there is a row for 7/11 on page 29.  The same scenario for 7/4, one on one page and another on the page following.
0
 
LinInDenverCommented:
The group footer should only appear once, at the end of your group. Are you seeing Group Footer 1 in the mix anywhere on the preview? (i.e.: is 7/11/2010 a part of a different Group 1 than 7/11/2011)?

In addition, You may want to right click on group 2 and change group - on the 2nd tab, click the box for "keep together"
0
 
garyjgsAuthor Commented:
I checked that and the rows for the group still go to a second page..
0
 
LinInDenverCommented:
would you mind posting a screenshot of the preview tab, with the section names (left hand side) in gray included?
0
 
garyjgsAuthor Commented:
See sample report.
9-27-2011-10-27-13-PM.pdf
0
 
garyjgsAuthor Commented:
See screen shot.
9-27-2011-10-29-42-PM.jpg
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.