Master Calendar Rolling Time Period Customizing

[kwarren1]

Hello,

I am trying to identify and flag the most recent N months, but I want them to be complete months of data. Therefore, if today is in the month of September, I would like build a flag that identifies the 12 months before September 2016, starting in August 2016 and going through Sept. 2015

MinMaxTemp:

LOAD

    MIN(OrderDate) as MinDate,

    MAX(OrderDate) as MaxDate

RESIDENT Orders

;

    LET vMinDate = NUM(PEEK('MinDate',0,'MinMaxTemp'));

    LET vMaxDate = NUM(PEEK('MaxDate',0,'MinMaxTemp'));

    LET vToday = $(vMaxDate)

;

CalTemp:

LOAD

    DATE($(vMinDate) + ROWNO() - 1) as TempDate

  AUTOGENERATE

          $(vMaxDate) - $(vMinDate) + 1;

DROP TABLE MinMaxTemp

;

MasterCalendar:

Load

    TempDate as Date,

    YEAR(TempDate) as Year,

    Month(TempDate) as Month,

    Month(TempDate)&'-'&Year(TempDate) AS MonthYear,

    Week(TempDate) as Week,

    Day(TempDate) as Day,

    'Q' & Ceil(Month(TempDate)/3) as Quarter,

    InYearToDate(TempDate,$(vToday),0) * -1 as CYTDFlag,

    InYearToDate(TempDate,$(vToday),-1) * -1 as LYTDFlag,  

    If(DayNumberOfYear(TempDate) <= DayNumberOfYear($(vToday)),1,0) as IsInYTD,

    If(DayNumberOfQuarter(TempDate) <= DayNumberOfQuarter($(vToday)),1,0,) as IsInQTD,

    If(Month(TempDate) = Month($(vToday)),1,0) as IsCurrentMonth,

    If(Month(AddMonths(TempDate,1)) = Month($(vToday)),1,0) as IsLastMonth,

    If(Month(AddMonths(TempDate,-1)) = Month($(vToday)),1,0) as IsFutureMonth,

    If((TempDate) > addmonths($(vMaxDate),-6) and (TempDate) <= Today(),1) as [Last6MonthsFlag],

    If((TempDate) > addmonths($(vMaxDate),-12) and (TempDate) <= Today(),1) as [Last12MonthsFlag]

  // If((TempDate) > addmonths($(vMaxDate),-6) and (TempDate) <= Today(),1) as [Last7-12MonthsFlag],

  // If((TempDate) > addmonths($(vMaxDate),-12) and (TempDate) <= Today(),1) as [Last13-24MonthCompletesFlag]

  // If((TempDate) > addmonths($(vMaxDate),-6) and (TempDate) <= Today(),1) as [LastComplete6MonthsFlag],

  // If((TempDate) > addmonths($(vMaxDate),-12) and (TempDate) <= Today(),1) as [LastComplete12MonthCompletesFlag]

  // If((TempDate) > addmonths($(vMaxDate),-6) and (TempDate) <= Today(),1) as [LastComplete7-12MonthsFlag],

  // If((TempDate) > addmonths($(vMaxDate),-12) and (TempDate) <= Today(),1) as [LastComplete13-24MonthCompletesFlag]

RESIDENT CalTemp

ORDER BY TempDate ASC

;

Drop Table CalTemp;

[Jason_Michaelides]

See this post:

Calendar with flags making set analysis so very simple

[sunny_talwar]

What is the issue that you are running into? What is getting included? It would be easier to point you in the right direction once we get to know about what is not working

[kwarren1]

Sorry for not clearly stating my direct issue above. I do not know how to write the following expression in my loadscript successfully. I am close, but my current output is flagging the last day of the 7th month, as well as the 6 complete months before the current month I am in.

    If((TempDate) > addmonths($(vMaxDate),-7) and (TempDate) <= addmonths($(vMaxDate),-1),1) as [LastComplete6MonthsFlag]

[sunny_talwar]

May be give this a try:

If(TempDate > MonthStart($(vMaxDate), -7) and TempDate <= MonthEnd($(vMaxDate), -1), 1) as [LastComplete6MonthsFlag]