Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jeckstein
Partner - Creator
Partner - Creator

Master Calendar Flags

   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.    

 

Screen Shot 2017-02-14 at 10.21.14 AM.png

Thanks

3 Replies
annafuksa1
Creator III
Creator III

Could you post your app and your current solution ?

jeckstein
Partner - Creator
Partner - Creator
Author

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
;
 

annafuksa1
Creator III
Creator III

Please check,

have commented your master calendar as it was easier for me