Create a previous quarter to date flag

calle21 — Thu, 05 Oct 2023 15:17:23 GMT

How do i add a previous quarter to date flag into this script?

// Calculate Min and Max Dates for Master Calendar
MinMaxTable:
LOAD
Min(Date) as mindate,
Max(Date) as maxdate
RESIDENT Fact;

LET vMinDate = Num(Peek('mindate', 0, 'MinMaxTable'));
LET vMaxDate = Num(Peek('maxdate', 0, 'MinMaxTable'));

// Temp Calendar for Date Generation
TempCalendar:
LOAD
$(vMinDate) + IterNo() - 1 AS NumDate
AUTOGENERATE 1
WHILE $(vMinDate) + IterNo() - 1 <= $(vMaxDate);

// Final Master Calendar
MasterCalendar:
LOAD
NumDate,
Date(NumDate) as Date,
Week(NumDate) as Week,
Year(NumDate) as Year,
Month(NumDate) as Month,
Day(NumDate) as Day,
'Q' & ceil(Month(NumDate) / 3) as Quarter,
Week(weekstart(NumDate)) & '-' & WeekYear(NumDate) as WeekYear,
WeekDay(NumDate) as WeekDay,
Dual(Year(NumDate)&'-'&Month(NumDate), monthstart(NumDate)) as YearMonth,
Dual(Year(NumDate)&'-Q'&Num(Ceil(Num(Month(NumDate))/3)),QuarterStart(NumDate)) as YearQuarter,
If(Day(NumDate) <= Day(Today()),1,0) as InMTD,
If(Year(NumDate) = Year(Today()) - 1, 1, 0) as Flag_LastYear,
If(Year(NumDate) = Year(Today()) - 1 and NumDate <= Today(), 1, 0) as Flag_LastYearToDate,
If(MonthStart(NumDate) = MonthStart(Today()), 1, 0) AS Flag_CurrentMonth,
If(MonthStart(NumDate) = MonthStart(AddMonths(Today(), -1)), 1, 0) AS Flag_LastMonth,
If(Year(NumDate) = Year(Today()), 1, 0) AS Flag_CurrentYear,
If(Year(NumDate) = Year(Today()), Month(NumDate), null()) as CurrentYearMonth,
If(NumDate > Today() - 30 and NumDate <= Today(), 1, 0) AS Flag_Last30Days, // New flag for last 30 days
If(NumDate > Today() - 60 and NumDate <= Today() - 30, 1, 0) AS Flag_Last60To30Days, // New flag for 30 days before last 30 days
If(Year(NumDate) = Year(Today()) and ceil(Month(NumDate) / 3) = ceil(Month(Today()) / 3), 1, 0) AS Flag_CurrentQuarter,
If(Year(NumDate) = Year(Today()) and ceil(Month(NumDate) / 3) = ceil(Month(Today()) / 3) - 1, 1, 0) AS Flag_PreviousQuarter,
If(Week(NumDate) = Week(Today()) - 1 and Year(NumDate) = Year(Today()), 1, 0) AS Flag_LastWeek,
-1 * InQuarterToDate(DATE#(NumDate), today(), -1 ) as IsPrevQTD

RESIDENT TempCalendar
ORDER BY NumDate ASC;

DROP TABLE TempCalendar;
DROP TABLE MinMaxTable;

topic Create a previous quarter to date flag in App Development

Create a previous quarter to date flag