INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# Business Objects: Crystal Reports 4 Other topics FAQ

## Common Formulas

 Working with rolling dates by HowardHammerman faq149-1220 Posted: 28 Oct 01 Often we encounter reporting requirements where we need to prompt the user for a starting or ending date and then display columns of information for a number of previous periods. I encountered this when working with a health care organization in Pennsylvania. They wanted to show revenue by month for the previous 12 months based on a date provided by a parameter field. I could not use Crystal's cross tabulation expert because many of the other calculations and accumulations were just too complex. I used Crystal array capability to solve the problem. The following formula simply provides the correct column heading for the second column in the report. The second column could be any of the twelve months depending on the value of the parameter field. //labmon2 stringVar array Months := ["Jan","Feb","Mar","Apr","May","Jun",                                         "Jul","Aug","Sep","Oct","Nov","Dec"];numberVar mon := Month({?High Date})-1;numberVar monF := IF mon <1 then 12+ mon;numberVar yearF := IF mon <1 then Year({?High Date}) -1                                else Year({?High Date});Months[monF]+" "+ToText (yearF,0,"")In the first line, I create a 12-position array of the month abbreviations. In the second line I create a memory variable, mon, which is equal to the month number of the parameter field's date minus 1. I am subtracting 1 because it is in the second column. For column 3 I would subtract 2, etc. In the third line I test to see if I have crossed the boundary between years. If High Date were equal to January, mon would be equal to 0. In that case monF would be equal to 12 + 0 = 12 or December. In the fourth line I do the same to find the correct year. The fifth line puts it all together as a text string. I used the same logic to calculate the proper amounts for each group footer line. Note that this will work for any date. Howard Hammerman, Ph.D.Hammerman Associates, Inc.http://www.hammerman.com800-783-2269Hammerman Associates, Inc. provide Crystal Reports training,consulting, course material, utilities and software. Consultants are available throughout North America for short or long-term assignments. Back to Business Objects: Crystal Reports 4 Other topics FAQ Index Back to Business Objects: Crystal Reports 4 Other topics Forum

Close Box

# Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!