Issue with creating monthend flag

joris_lansdaal — Sun, 29 May 2016 16:39:12 GMT

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);

Re: Issue with creating monthend flag

swuehl — Sun, 29 May 2016 19:35:30 GMT

Monthend() will return a value corresponding to the last millisecond of the last date in the month...

if(TempDate=Floor(MonthEnd(TempDate)),1,0) as Monthend_Flag

Re: Issue with creating monthend flag

MarcoWedel — Sun, 29 May 2016 19:40:26 GMT

Hi,

other solutions for the condition might be:

TempDate=DayName(MonthEnd(TempDate))

TempDate=DayStart(MonthEnd(TempDate))

hope this helps

regards

Marco

Re: Issue with creating monthend flag

MarcoWedel — Sun, 29 May 2016 19:44:59 GMT

and as boolean false and true ahve the numeric values 0 and -1 an alternative to the if() function could be:

-(TempDate=DayName(MonthEnd(TempDate))) as Monthend_Flag

hope this helps

regards

Marco

Re: Issue with creating monthend flag

joris_lansdaal — Sun, 29 May 2016 20:25:50 GMT

Thanks!

Re: Issue with creating monthend flag

joris_lansdaal — Sun, 29 May 2016 20:26:04 GMT

Thank you!

Re: Issue with creating monthend flag

MarcoWedel — Sun, 29 May 2016 21:22:33 GMT

You're welcome

regards

Marco

topic Re: Issue with creating monthend flag in QlikView

Issue with creating monthend flag

Re: Issue with creating monthend flag

Re: Issue with creating monthend flag

Re: Issue with creating monthend flag

Re: Issue with creating monthend flag

Re: Issue with creating monthend flag

Re: Issue with creating monthend flag