Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I'm a bit lost in my current issue. I want to create a simple monthendflag. Monthstart works fine, but monthend.....
if(TempDate=MonthEnd(TempDate),1,0) as Monthend_Flag
if(TempDate=Monthstart(TempDate),1,0) as Monthstart_Flag
Anyone advise?
Below is my Master Calendar.
QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);
Let varMinDate = Num('01-01-2013');
Let varMaxDate = Round(Num(Monthend(Today(),+12))) ;
// We starten de historie opbouw vanaf 1 jan 2013
// De MasterCalender zal gebruikt worden voor alle kubussen dus bevat ook dagen 12 maanden voorruit tbv Forecasting.
// Datum is de naam voor de de koppeldimensie
TempCalendar:
Load
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate) ;
MasterCalendar:
Load TempDate as Datum,
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day (TempDate) As Day,
date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,
inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay,
Day(TempDate)& '-' & WeekYear(TempDate) as DayYear,
// Ten behoeve van verschillende rapportages zullen Flag gemaakt worden.
// Deze worden gemaakt tov de systeemdatum
If(TempDate >= Monthstart(Today(),-12) and TempDate <= Today()-1,1,0) as LastMNDFlag,
If(TempDate >= Monthstart(Today()) and TempDate <= Today()-1,1,0) as CurMNDFlag,
If(TempDate >= Monthstart(Today(),-13) and TempDate <= Monthend(Today(),-13),1,0) as LastMTDFlag, // Een maand voor systeemdatum omdat we volledige maanden nemen
If(TempDate >= Monthstart(Today(),-1) and TempDate <= Monthend(Today(),-1),1,0) as CurMTDFlag, // Een maand voor systeemdatum omdat we volledige maanden nemen
If(TempDate >= Yearstart(Monthstart(Today(),-1),0) and TempDate <= Monthend(Today(),-1),1,0) as CurYTDFlag,
If(TempDate >= Yearstart(Monthstart(Today(),-1),-1) and TempDate <= Monthend(Today(),-13),1,0) as LastYTDFlag,
if(TempDate>= Monthstart(Monthstart(Today(),0),0)and TempDate<= Monthend(Monthstart(Today(),+11),0),1,0) as NextMATFlag,
if(TempDate>= Monthstart(Monthstart(Today(),-1),-11)and TempDate<= Monthend(Monthstart(Today(),-1),0),1,0) as CurMATFlag,
if(TempDate>= Monthstart(Monthstart(Today(),-1),-23)and TempDate<= Monthend(Monthstart(Today(),-1),-12),1,0) as LastMATFlag,
if(TempDate>= Monthstart(Monthstart(Today(),0),0)and TempDate<= Monthend(Monthstart(Today(),+2),0),1,0) as Next3MFlag,
if(TempDate>= Monthstart(Monthstart(Today(),0-12),0)and TempDate<= Monthend(Monthstart(Today(),+2-12),0),1,0) as Cur3MFlag,
if(TempDate>= Monthstart(Monthstart(Today(),0-24),0)and TempDate<= Monthend(Monthstart(Today(),+2-24),0),1,0) as Last3MFlag,
if(TempDate>= Monthstart(Monthstart(Today(),-1),-2)and TempDate<= Monthend(Monthstart(Today(),-1),0),1,0) as CurQFlag,
if(TempDate>= Monthstart(Monthstart(Today(),-1),-14)and TempDate<= Monthend(Monthstart(Today(),-1),-12),1,0) as LastQFlag,
if(TempDate>= Monthstart(Monthstart(Today(),-1),-14)and TempDate<= Monthend(Monthstart(Today(),-1),0),1,0) as M15Flag,
if(TempDate>= Monthstart(Monthstart(Today(),-1),-24)and TempDate<= Monthend(Monthstart(Today(),-1),0),1,0) as M25Flag,
if(TempDate>= Today() and TempDate <= Monthend(Today(),+6),1,0) as ReplenishFlag,
if(TempDate=MonthEnd(TempDate),1,0) as Monthend_Flag,
if(TempDate=Monthstart(TempDate),1,0) as Monthstart_Flag
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;
Store MasterCalendar into $(vQVDPath)\MasterCalendar.QVD (QVD);
Accepted Solutions
