Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Date format problem

I want to create a variable IsMonthEnd in calendar table which indicates 1 if its a month end date and 0 otherwise. Below is my calendar code. I have tried several ways but it produces all 0 in the variable IsMonthEnd.

Let EndDate = num(Date(Today(),'MM/DD/YYYY'));

Let StartDate = num(makedate(2008,1,1));

Let CurrentDate=num(date(Today(),'MM/DD/YYYY'));

Let vCurrentYear=Year(Today());

Let LY_YTD=num(AddMonths(Today(),-12,0));

Let DaysToEnd=Monthend(Today())-Today();

Let Timespan=Year(Today())-Year(Date($(StartDate)));

Let ToDate = Num(Today());

Let FromDate = Num(Today()-1);

Let QToDate = Ceil(Month(DATE(today()))/3);

Calender:

Load

          *,

          Date as Datex,

          Year(Date) AS ,

          Month(Date) AS ,

          num(Month(Date)) AS [MonthNum],

          num(Week(Date),00) AS [Week],

          Day(Date) AS [Day],

          WeekDay(Date) AS [WeekDay],

          Year(Date)&num(Month(Date),00) AS [Year Month2],

          if(Date<=$(CurrentDate),Date) AS Date2,// Dates until Current date, used for balance dates

          Date(Monthstart(Date),'MMM-YY') AS [RollMonth],

          Year(Date)&num(Month(Date),00) AS [Year Month],

          Dual(WeekYear(Date)&'W'&num(Week(Date),00),WeekYear(Date)&num(Week(Date),00)) AS [Year Week2],

          Year(Date)&num(Week(Date),00) AS [Year Week],

          Dual('T'&Num(Ceil(Num(Month(Date))/3)),Num(Ceil(NUM(Month(Date))/3),00)) AS ,

          Year(Date)&Dual('Q'&Num(Ceil(Num(Month(Date))/3)),Num(Ceil(NUM(Month(Date))/3),00)) AS [Year Quarter],

          Dual(Num(Ceil(Num(Month(Date))/3)),Num(Ceil(NUM(Month(Date))/3),00)) AS Quarter,

          WeekEnd(Date)-1 as WeekEnd, //date -1 for week to end on Saturday

          if(Date = MonthEnd(Date), 1,0) As IsMonthEnd

          ;

Load

           Date($(StartDate)+(Iterno()-1),'MM/DD/YYYY') AS Date

           // Date, to be used as key feild to transactions

Autogenerate 1

While Date($(StartDate)+(Iterno()-1)) <=Date($(EndDate))

;

What am i doing wrong?

I have daily observations. My objective is to plot only last day of month data in a line chart. Once i get this variable then i can use set analysis to create an expression. Also suggest if there is a better way to plot month end data. 

1 Solution

Accepted Solutions

Re: Date format problem

Hi try like this

if(match(Date,Date(MonthEnd(Date),'MM/DD/YYYY')),1,0) As IsMonthEnd

2 Replies

Re: Date format problem

Hi try like this

if(match(Date,Date(MonthEnd(Date),'MM/DD/YYYY')),1,0) As IsMonthEnd

Not applicable

Re: Date format problem

That worked like a magic. Thanks a lot.

Community Browser