Crystal Reports - Rolling 12 Month with Prior Year

Erin Leale
Erin Leale used Ask the Experts™
on
Hello,

The attached report works great - now what the sales staff would like is the current criteria as shown but to add the prior year data below each month.  For example - Jul 17 Gallons then in a section directly below would have Jul 16 gallons.  I thought I could modify the formula by copying and just adding -1 to the year but that didn't do the trick.  Any help is greatly appreciate!

Thank you.
Sales.rpt
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Did you also change the record selection formula to include the prior year?  The formula in the report that you posted doesn't seem to include it.  That might be all that you need.

 Of course any place that references those figures (and possibly other fields) will need to check the year, so that only the correct year is included.

 James

 Edit:
 When you said "modify the formula", I assumed that you were talking about formulas like zMonth01.  if you were actually talking about the record selection formula, could you post the formula that you tried?
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Your selection formula will be

{AR_InvoiceHistoryHeader.InvoiceDate} >= DateAdd('m', -12, {?StartDate})  AND
{AR_InvoiceHistoryHeader.InvoiceDate} < DateAdd('m', 12, {?StartDate})

Open in new window


You are going to have to "duplicate the 12 calculate formulas as
zMonth1last through zMonth12last
if year({AR_InvoiceHistoryHeader.InvoiceDate}) = Year(DateAdd('m',0,{?StartDate}))-1
and
month({AR_InvoiceHistoryHeader.InvoiceDate}) = Month(DateAdd('m',0,{?StartDate})) then 
    {AR_InvoiceHistoryDetail.ExtensionAmount} 
else 
    0

Open in new window


Use them in the same way and add the summaries to each group footer

mlmcc
Erin LealeConsultant/Controller

Author

Commented:
Hi mlmcc -

I added the formulas and changed the select criteria - after I changed the select criteria I receive the attached error.

Thanks.
12-month-rolling-error.jpg
Fuel-Sales-Rolling-12-with-prior-yea.rpt
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Try this change the zCalcTotals to

WhilePrintingRecords;
Global NumberVar Array arrCustGallons;
GLobal NumberVar Array arrCustGallonsLast;

Local NumberVar MonthIndex;

If {?StartDate} <=  {AR_InvoiceHistoryHeader.InvoiceDate} then
(
    MonthIndex := DateDiff('m',{?StartDate}, {AR_InvoiceHistoryHeader.InvoiceDate}) + 1;
    arrCustGallons[MonthIndex] := arrCustGallons[MonthIndex] + {AR_InvoiceHistoryDetail.ExtensionAmount}
)
Else
(
    MonthIndex := 13 + DateDiff('m',{?StartDate}, {AR_InvoiceHistoryHeader.InvoiceDate});
    arrCustGallonsLast[MonthIndex] := arrCustGallonsLast[MonthIndex] + {AR_InvoiceHistoryDetail.ExtensionAmount}
)

Open in new window


Change the zDeclVariables

WhilePrintingRecords;
Global DateVar Array arrPDates;
Global NumberVar Array arrCustGallons;
Global NumberVar Array arrSalesPGallons;
Global NumberVar Array arrCityStateGallons;
Global NumberVar Array arrCustGallonsLast;

Local NumberVar Index;

ReDim arrPDates[12];
ReDim arrCustGallons[12];
ReDim arrSalesPGallons[12];
ReDim arrCityStateGallons[12];
Redim arrCustGallonsLast[12];

For Index := 1 to 12 do
(
    arrPDates[Index] := Date(DateAdd('m',Index - 1,{?StartDate}));
    arrCustGallons[Index] := 0;
    arrSalesPGallons[Index] := 0;
    arrCityStateGallons[Index] := 0;
    arrCustGallonsLast[Index] := 0;
);
''

Open in new window


mlmcc
Erin LealeConsultant/Controller

Author

Commented:
Thank you!  I believe that did the trick - I will be checking the gallons to verify.

Thanks for your help!
Hopefully mlmcc has provided your solution.

 If you don't know, the error was because your DateDiff operation would give you negative results for the previous 12 months, so you'd end up with a 0 or negative array subscript (MonthIndex).  mlmcc handled that my using two different DateDiff operations, depending on InvoiceDate.  Another way to handle it would be to use your adjusted starting date -- DateAdd('m', -12, {?StartDate}) -- in the DateDiff operation.  Then you'd get 1 to 24 for the index, for the 24 months.  Of course your array(s) would have to be expanded accordingly.

 I also wanted to check one thing.

 What kind of dates do you use for {?StartDate} ?  Is it always the first day of the month, or could it be any day in the month?  It seems to be a normal date parameter, so I assume that it could be any day in the month.  If so, then you may have a problem.  A DateDiff in months between one day and an earlier day in the same month (eg. DateDiff ('m', #01/18/2018#, #01/01/2018#) ) will give you 0.  In that case, mlmcc's formula would produce a MonthIndex of 13, which would probably give you an error, since the array was set to have 12 elements.

 If the date is supposed to be the first of the month, but the user could select/enter other dates, then you could handle it by checking {?StartDate} and, if necessary, changing the date to the first of the month.

 James
Senior Consultant
Most Valuable Expert 2011
Top Expert 2013
Commented:
In the related question that produced the original solution, he indicated they always choose the 1st of the month thus I didn't adjust for t in the original formula

mlmcc
Erin LealeConsultant/Controller

Author

Commented:
That did the trick!  Thanks!

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