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