Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
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

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