Avatar of Taras
Taras
Flag for Canada asked on

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

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.
Crystal Reports

Avatar of undefined
Last Comment
Raghavendra Hullur

8/22/2022 - Mon
Raghavendra Hullur

Hi,

I think it will return 28th February 2018 as last year's start date even though start date is 29th March 2019.

You can create a test formula with content Date(DateAdd("m",-13,cDate(2019,03,29))) and check it.
Taras

ASKER
Does it mean that DateAdd('m'-1,CurrentDate) or DateAdd('m',1, CurrentDate) will not always  give you the same date,  as current date could be  29,30,31 and February has 28 and leap year 29. The similar is with othere months that have 30 days(April, June, Sep..)

 When you subtracting or adding month or months in DateAdd function if the month that you are coming to has less days than month you are coming from  you come to the last day of the month not to that date from witch you are going back or forward as that date do not exist in that month.
Is that right?
Raghavendra Hullur

It always gives the last date of February if the current year's date is greater than 28 or 29 in leap year.
For other months, it will give the same date for previous year as in current year.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Raghavendra Hullur

31/03/2019 gives 28/02/2018
28/03/2019 gives 28/02/2018
28/03/2021 gives 28/02/2020
30/03/2021 gives 29/02/2020 because 2020 is a leap year.
Taras

ASKER
It is not just March February issue it is May 31 April 30 and all other months that do not have 31 as Date going back or going forward does not matter.
Which date you will get if you add DateAdd('m'1,CurrentDate) if Current Date is 09/31/2019??
I am asking is that accurate? You are giving me only answer for March,- February. I think this issue is connected with other months that have this pattern. Can you confirm that?
ASKER CERTIFIED SOLUTION
Raghavendra Hullur

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.