Last 5 complete Quarter Flag

jeckstein — Wed, 25 Nov 2020 16:16:04 GMT

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;

Re: Last 5 complete Quarter Flag

sunny_talwar — Thu, 11 May 2017 14:45:31 GMT

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

Re: Last 5 complete Quarter Flag

antoniotiman — Thu, 11 May 2017 15:03:23 GMT

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

Re: Last 5 complete Quarter Flag

jeckstein — Thu, 11 May 2017 16:22:58 GMT

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

Re: Last 5 complete Quarter Flag

jeckstein — Thu, 11 May 2017 16:41:37 GMT

This only flags the past 4 quarters.

Re: Last 5 complete Quarter Flag

antoniotiman — Thu, 11 May 2017 16:52:07 GMT

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;

Re: Last 5 complete Quarter Flag

jeckstein — Thu, 11 May 2017 17:55:34 GMT

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

topic Re: Last 5 complete Quarter Flag in QlikView

Last 5 complete Quarter Flag

Re: Last 5 complete Quarter Flag

Re: Last 5 complete Quarter Flag

Re: Last 5 complete Quarter Flag

Re: Last 5 complete Quarter Flag

Re: Last 5 complete Quarter Flag

Re: Last 5 complete Quarter Flag