Date format problem

Tue, 14 Aug 2012 19:20:58 GMT

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.

Re: Date format problem

MayilVahanan — Tue, 14 Aug 2012 20:47:16 GMT

Hi try like this

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

Re: Date format problem

Tue, 14 Aug 2012 20:57:39 GMT

That worked like a magic. Thanks a lot.

topic Re: Date format problem in QlikView

Date format problem

Re: Date format problem

Re: Date format problem