troubleshooting Question

Comparing This with Last Year - Rolling month, 3month, Year

Avatar of Taras
TarasFlag for Canada asked on
Crystal Reports
6 Comments1 Solution105 ViewsLast Modified:
I am working on old report created by somebody else. Report is supposed to do rolling month, 3m, Year comparing with a previous Year month, 3m,Year.
I have a question regarding calculating dates and want to understand it better.
Client asked for setup like If run date is 28/03/2019 that rolling month range should be something as:
This year : 28/02/2019 – 28/03/2019.
And
Last Year: 28/02/2018 – 28/03/2018.
Similar patern for month and Year.

What is now in report for calculating This Year Month and Previous Year month is here:
//This Year Month
NumberVar Amount;
Amount:=0;

If Year({@InvoiceDate}) = Year({?Month Of Report}) And  Month({@InvoiceDate}) = Month({?Month Of Report}) then
Amount:= {Invoice.Amount}
Else
Amount:=0;
Amount;

//Last year month

NumberVar LastYearMonthAmount;

If Year({@InvoiceDate}) = Year({?Month Of Report})-1 And  Month({@InvoiceDate}) = Month({?Month Of Report}) then
LastYearMonthAmount:= {Invoice.Amount}
Else
LastYearMonthAmount:=0;
LastYearMonthAmount;

If I understand properly this formula will not compare the same range if report is run on 03/28/2019.

In this year it will take Date 03/01/2019 to 03/28/2019
But in Last Year it will take 03/01/2018 to 03/31/2018
Am I Right??


To meet client request I created something as this regarding rolling this year month and last Year month :
NumberVar Amount;
Amount:=0;
DateVar SMonth;
DateVar EMonth;
Datetimevar RunDate;
DateTimeVar StartDate;
RunDate:= {?Month Of Report};
SMonth:= Date(Year(RunDate),Month(RunDate),Day(RunDate));
EMonth:= Date(Year(RunDate),Month(RunDate),Day(RunDate));

If
({@InvoiceDate} >= dateserial(Year(sMonth),Month(sMonth)-1,Day(sMonth))
And
{@InvoiceDate}< dateserial(year(EMonth),Month(EMonth),Day(EMonth)))  
 then
Amount:={Invoice.Amount}
Else
Amount:=0;
Amount;





NumberVar LastYearMonthAmount;
LastYearMonthAmount:=0;

DateVar SMonth;
DateVar EMonth;

Datetimevar RunDate;
DateTimeVar StartDate;
DateTimeVar EndDate;

StartDate:= {?Month Of Report};
StartDate:= Date(DateAdd("m",-13,StartDate));
SMonth:= Dateserial(Year(StartDate),Month(StartDate),Day(StartDate));

EndDate:= DateAdd("m",1,StartDate);
EMonth:= Dateserial(Year(EndDate),Month(EndDate),Day(EndDate));

If
({@InvoiceDate}>= SMonth And {@InvoiceDate} < EMonth ) then
      LastYearMonthAmount:={Invoice.Amount}
Else
      LastYearMonthAmount:=0;
      LastYearMonthAmount;

Which date range I will have If I run this formula on 03/29/2019
As month February has 28 days.
ASKER CERTIFIED SOLUTION
Raghavendra Hullur
Software Developer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros