Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Case Portal has moved to Qlik Community! Read the FAQs to start exploring Support resources.
cancel
Showing results for 
Search instead for 
Did you mean: 
joris_lansdaal
Creator
Creator

Issue with creating monthend flag

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

Monthend_flag.PNG

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

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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

View solution in original post

6 Replies
swuehl
MVP
MVP

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

View solution in original post

MarcoWedel

Hi,

other solutions for the condition might be:

TempDate=DayName(MonthEnd(TempDate))

or

TempDate=DayStart(MonthEnd(TempDate))



hope this helps


regards


Marco

MarcoWedel

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

joris_lansdaal
Creator
Creator
Author

Thanks!

joris_lansdaal
Creator
Creator
Author

Thank you!

MarcoWedel

You're welcome

regards

Marco