2 Replies Latest reply: Aug 14, 2012 4:57 PM by samarth.shah RSS

    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 [Y],

                Month(Date) AS [M],

                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 [T],

                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.