Master Calendar Rolling Time Period Customizing

kwarren1 — Tue, 06 Sep 2016 16:47:05 GMT

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;

Re: Master Calendar Rolling Time Period Customizing

Jason_Michaelides — Tue, 06 Sep 2016 17:10:21 GMT

See this post:

Calendar with flags making set analysis so very simple

Re: Master Calendar Rolling Time Period Customizing

sunny_talwar — Tue, 06 Sep 2016 23:05:12 GMT

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

Re: Master Calendar Rolling Time Period Customizing

kwarren1 — Wed, 07 Sep 2016 13:52:26 GMT

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]

Re: Master Calendar Rolling Time Period Customizing

sunny_talwar — Wed, 07 Sep 2016 22:34:27 GMT

May be give this a try:

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

topic Re: Master Calendar Rolling Time Period Customizing in QlikView

Master Calendar Rolling Time Period Customizing

Re: Master Calendar Rolling Time Period Customizing

Re: Master Calendar Rolling Time Period Customizing

Re: Master Calendar Rolling Time Period Customizing

Re: Master Calendar Rolling Time Period Customizing