Crystal Reports - Rolling 12 Month with Prior Year

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
Erin LealeConsultant/ControllerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

James0628Commented:
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?
0
mlmccCommented:
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
0
Erin LealeConsultant/ControllerAuthor 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
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

mlmccCommented:
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
0
Erin LealeConsultant/ControllerAuthor Commented:
Thank you!  I believe that did the trick - I will be checking the gallons to verify.

Thanks for your help!
0
James0628Commented:
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
0
mlmccCommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Erin LealeConsultant/ControllerAuthor Commented:
That did the trick!  Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.

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.