Skip to main content
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
jeckstein
Partner - Creator
Partner - Creator

Last 5 complete Quarter Flag

Hi I have the following master calendar script. I need a flag that will flag the last 5 complete quarters. Any thoughts?

MinMax:
LOAD
Min([ReportingDate]) as MinDate,
max([ReportingDate]) as MaxDate // use the reporting date as a reference for the month to display as prior
//today() as MaxDate //use today's date as a reference for the month to display as prior
Resident
Fact;


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


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 [ReportingDate],
Date(WeekStart(TempDate),'M/D/YYYY') as WeekStart,
Date(WeekStart(TempDate), 'M/D/YY') as WeekStart2,
Week(TempDate) as Week,
Year(TempDate) as Year,
chr(39) & right(Year(TempDate),2) as [Short Year],
Month(TempDate) as Month,
AutoNumber(Date(monthstart(TempDate),'MMM-YY')) as MonthID,
MonthStart(TempDate) as MonthStart,
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,
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,
if(date(TempDate) >= Date(AddMonths(Today(0),-12)), '1') as Rolling12Mo,
if(date(TempDate) >= Date(AddMonths(MonthStart('$(vReportRunDate)'),-6)) and date(TempDate)< Date(MonthStart('$(vReportRunDate)')), '1') as Rolling6Mo,     //Change 001---3/8/2017
if(date(TempDate) >= Date(Today(0)-7), '1') as Rolling7Day,
if(date(TempDate) = Date(Today(0)-2), '1') as PreviousDay,
if(date(TempDate) >= Date(Today(0)-90), '1') as RollingAvg,
DayNumberOfYear(TempDate) as DayNumberOfYear,
WeekStart( TempDate, 0, 5 ) as WeekStartSaturday,
WeekEnd( TempDate, 0, 5 ) as WeekEndFriday,
WeekStart( TempDate, 0, 5 ) & ' - ' &
WeekEnd( TempDate, 0, 5 ) as WeekInterval

Resident TempCalendar
Order By TempDate ASC;

//Delete temp table
Drop Table TempCalendar;

6 Replies
sunny_talwar

May be this

If(Floor(QuarterEnd(Today())) = Today(),

If(QuarterStart(Today(), -4) >= TempDate and QuarterEnd(Today()) <= TempDate, 1, 0),

If(QuarterStart(Today(), -5) >= TempDate and QuarterEnd(Today(), -1) <= TempDate, 1, 0)) as Last5QuarterFlag

antoniotiman
Master III
Master III

Pick(Match(TempData,QuarterEnd(Today()),QuarterEnd(Today(),-1),QuarterEnd(Today(),-2),QuarterEnd(Today(),-3),QuarterEnd(Today(),-4)),1,2,3,4,5) as Flag

jeckstein
Partner - Creator
Partner - Creator
Author

This did not work. The only value for the flag was 0.

jeckstein
Partner - Creator
Partner - Creator
Author

This only flags the past 4 quarters.

antoniotiman
Master III
Master III

Try

Pick(Match(TempData,QuarterEnd(Today(),-1),QuarterEnd(Today(),-2),QuarterEnd(Today(),-3),QuarterEnd(Today(),-4),QuarterEnd(Today(),-5)),1,2,3,4,5) as Flag;

jeckstein
Partner - Creator
Partner - Creator
Author

This gave me 5 quarters but when selecting the flag only the last Month in each of the quarters is associated.