Change Calendar Month (May) to Start on 26th of previous month (April) and end on the 25th of May

[Anonymous]

I want to change the Month Start and Month End dates.

For example:

                     The Month is MAY

                          The start of the month is the 26th of April

                          The end of the month is the 25th of May

I want this to be the case for all Months.

[swuehl]

I assume you don't want to start all Months on the 26th of April, but on the 26th of the prev month.

Maybe like this:

LOAD Date,

Date(monthstart(Date-25,1),'MMM-YYYY') as Month;

LOAD

Date(makedate(2012)+recno()-1) as Date

AutoGenerate 366;

[Anonymous]

This only seems to work for the year 2012 and beyond.
I need it to work for previous years as well

[swuehl]

Why do you think so? Can you give me a sample date where my above expression

Date(monthstart(Date-25,1),'MMM-YYYY') as Month;

fails?

The LOAD generating 366 Date values is only added to my sample to create some sample data, you can of course use other input data / date ranges. I see no issue with using years different from 2012 and beyond.

[Anonymous]

so should I just be using the following in my calendar script?

Date(monthstart(Date-25,1),'MMM-YYYY') as Month;

This is what my Calendar script looks like

//----------------------------------------------------------------------------------------------

// Master Calendar

//----------------------------------------------------------------------------------------------

DateTmp:

LOAD FieldValue('DocumentDateID', RowNo()) as %_DateKey

AutoGenerate(FieldValueCount('DocumentDateID'));

//DateTmp:

//Load Distinct DocumentDateID as %_DateKey

//Resident Transactions;

DateTmpII:

NoConcatenate

LOAD SubField(%_DateKey, '|', 2) as %_DateKey

Resident DateTmp

Where %_DateKey <> 'D|';

//EXIT SCRIPT;

DROP Field DocumentDateID From Transactions;

DateTemp:

NoConcatenate

LOAD %_DateKey

RESIDENT DateTmpII

ORDER BY %_DateKey Asc;

LET vMinDate = Num(Peek('%_DateKey', 0, 'DateTemp'));

LET vMaxDate = Num(Peek('%_DateKey', -1, 'DateTemp')); 

DROP Table DateTmp, DateTmpII;

DROP Table DateTemp;

TempCalendar:

NoConcatenate

LOAD

$(vMinDate)+IterNo()-1 as Num,

Date($(vMinDate)+IterNo()-1) as TempDate

AUTOGENERATE 1 WHILE ($(vMinDate)+IterNo()-1) <= $(vMaxDate);

//EXIT Script;

//----------------------------------------------------------------------------------------------

// Temporary Calendar

//----------------------------------------------------------------------------------------------

MasterCalendar:

Left Keep (Transactions)

LOAD TempDate as Date,

           Date(Date#(TempDate,'DD/MM/YYYY'),'DD/MM/YYYY') as %_DateKey,

     week(TempDate) as Week,

     year(TempDate) as Year,

     month(TempDate) as Month,

     day(TempDate) as Day,

     weekday(TempDate) as WeekDay,

    'Q' & ceil(month(TempDate)/3) as Quarter,

     year(TempDate)&'-'&'Q' & ceil(month(TempDate)/3) as YearQuarter,

     date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,

     date(monthstart(TempDate), 'MMMYY') as MonthYearShort,

     week(TempDate)&'-'&Year(TempDate) as WeekYear,

//     Year2Date(TempDate, 0, 1, $(vToday))*-1 as CurYTDFlag,

//     Year2Date(TempDate,-1, 1, $(vToday))*-1 as LastYTDFlag,

     num(month(TempDate),'00') as Period,

//     AutoNumber(monthstart(TempDate)) as PeriodCount,

//     DayNumberOfYear(TempDate) as DayNumberOfYear,

//     yearname(TempDate,0,4) as FinYear,

     Year(TempDate) & num(month(TempDate),'00') as YearMonth

// ------------------------------------------------------------------

RESIDENT TempCalendar 

ORDER BY TempDate Asc;

DROP TABLE TempCalendar;

[CELAMBARASAN]

You can check with this

Month(AddMonths(TempDate,If(Day(TempDate)>25,1,0))) as Month,

[swuehl · Accepted solution]

I think you should be fine using

     Month(monthstart(TempDate-25,1)) as Month,

in your MasterCalendar load. (I replaced the Date function with Month, so you will get the dual month values as is).