Master Calendar Flags

jeckstein — Wed, 15 Feb 2017 15:07:12 GMT

I am looking to make the following master Calendar Flags. The flags should cover a two year period. The Column2 flag should use the following logic. Flag everything two years prior to the last full quarter. Column1 should use the following logic, Flag everything two years prior to the second to last full quarter. The image should below shows the desired output.

Column 3 shows what the column 2 flag should be after we reach the end of Q1 in 2017.

Thanks

Re: Master Calendar Flags

annafuksa1 — Wed, 15 Feb 2017 15:43:33 GMT

Could you post your app and your current solution ?

Re: Master Calendar Flags

jeckstein — Wed, 15 Feb 2017 16:12:13 GMT

I cannot post the app, but here is the script for the Master Calendar I am using.

Temp_3;
//Encounters;

Let vMinDate = Num(Peek('MinDate',0,'MinMax'));
Let vMaxDate = Num(Peek('MaxDate',0,'MinMax'));

//IMPORTANT NOTE: TODAY IS POINTING TO YESTERDAY, THE MAXIMIMUM DATA WITHIN THE APPLICATION SHOULD BE LIMITED TO THE LAST FULL DAY OF DATA (YESTERDAY)
Let vToday = num(Today()-1);

drop Table MinMax;

//Autogenerate a source table for your calendar
TempCalendar:
Load
$(vMinDate) + RowNo() - 1 as Num,
Date($(vMinDate) + RowNo() - 1) as TempDate
Autogenerate
$(vMaxDate) - $(vMinDate) + 1;

//Generate the Master Calendarå
MasterCalendar:
Load
Autonumber('|'&Date(TempDate,'YYYYMM')) as DateNum,
Date(Floor(TempDate)) as Date,
Date(WeekStart(TempDate),'M/D/YYYY') as WeekStart,
Date(WeekStart(TempDate), 'M/D/YY') as WeekStart2,
Week(TempDate) as Week,
Year(TempDate) as Year,
AutoNumber(Date(YearStart(TempDate))) as YearID,
chr(39) & right(Year(TempDate),2) as [Short Year],
Month(TempDate) as Month,
MonthStart(TempDate) as MonthStart,
MonthEnd(TempDate) as MonthEnd,
Year(Floor(TempDate)) &
If(Len(Num(Month(Floor(TempDate))))=1, 0 &
Num(Month(Floor(TempDate))),
Num(Month(Floor(TempDate)))) as Period,
Num(Month(TempDate), '00') as MonthNum,
Day(TempDate) as Day,
WeekDay(TempDate) as WeekDay,
'Q' & ceil(Month(TempDate)/3) as Quarter,
'Q' & ceil(Month(TempDate)/3)&'-'&
Right(Year(TempDate),2) as QuarterYear,
Date(monthstart(TempDate),'MMM-YY') as MonthYear,
AutoNumber(Date(monthstart(TempDate),'MMM-YY')) as MonthYearID,
//If(TempDate>$(vToday),1,0) as FutureFlag,
If(TempDate>$(vToday),1,0) as FutureFlag,
If( InMonth (TempDate, $(vToday), +1),1) as NextMonthFlag,
Week(TempDate) & '-' & Right(Year(TempDate), 2) as WeekYear,
Right(Year(TempDate), 2) & '-' & Week(TempDate) as YearWeek,
inyeartodate(TempDate,$(vToday),0) * -1 as CurYTDFlag,
inyeartodate(TempDate,$(vToday),-1) * -1 as LastYTDFlag,
inmonthtodate(TempDate,$(vToday),0) * -1 as CurMTDFlag,
inmonthtodate(TempDate,$(vToday),+1) * -1 as NextMTDFlag,
inmonthtodate(TempDate,$(vToday),-1) * -1 as LastMTDFlag,
If( InMonth (TempDate, $(vToday),0),1) as This_Month_Flag, // All Dates This Month This Year
If( InMonth (TempDate, $(vToday),-1),1) as Last_Month_Flag, // All Dates This Month This Year
If( InMonth (TempDate, $(vToday),-12),1,0) as LYMTDFlag,
if(date(TempDate) >= Date(AddMonths(Today(0),-12)), '1') as Rolling12Mo,
if(date(TempDate) >= Date(Today(0)-30), '1') as Rolling30Day,
if(date(TempDate) = Date($(vToday)), '1') as PreviousDay,
if(date(TempDate) >= Date(Today(0)-90), '1') as RollingAvg,
If(TempDate >= YearStart(Today(1), -1) and TempDate <= MonthEnd(Today(1), -12), 1, 0) as LY_YTD_Monthend,
If(InMonthToDate (TempDate, $(vToday),-12),1) as MTD_LY_FLAG // All Dates This Month To Date Last Year
Resident TempCalendar
Order By TempDate ASC;

//Delete temp table
Drop Table TempCalendar;

left join (MasterCalendar)
load distinct CurMTDFlag
, if(CurMTDFlag=0,'No','Yes') as Current_MTD_Flag
Resident MasterCalendar
;

left join (MasterCalendar)

load Date
, NetWorkDays(Date+1,MonthEnd) as Proj_Remaining_Weekday_Days_In_Month //+1 logic is needed to not count current day
, (num(floor(MonthEnd)) - num(floor(Date))) as Proj_Days_left_in_month
, (num(floor(MonthEnd)) - num(floor(Date))) //Days left in month
- NetWorkDays(Date+1,MonthEnd) //minus week days left in month
as Proj_Remaining_Weekend_Days_In_Month
Resident MasterCalendar
where Date = Today()-1
;

Re: Master Calendar Flags

annafuksa1 — Wed, 15 Feb 2017 16:32:37 GMT

Please check,

have commented your master calendar as it was easier for me

topic Re: Master Calendar Flags in QlikView

Master Calendar Flags

Re: Master Calendar Flags

Re: Master Calendar Flags

Re: Master Calendar Flags