0 Replies Latest reply: Jan 21, 2010 11:21 AM by beciafreestyle RSS

    MonthToDate value on minus

    beciafreestyle

      Dear QlikView users,

      I have kind of funny thing, cause my MonthToDate sales are on minus (the same QuarterToDate), i used basic script:

      Year2Date(TempDate, 0, Month(Today())) as CurMTDFlag,
      Year2Date(TempDate, -1, Month(Today())) as LastMTDFlag

      the same for quarters. My Master Calendar looks as following:

      LET vDateMin = Num(MakeDate(2008,1,1));
      LET vDateMax = Floor(YearEnd(AddMonths(Today(), 1)));
      LET vDateToday = Num(Today());

      TempCalendar:
      LOAD
      $(vDateMin) + RowNo() - 1 AS DateNumber,
      Date($(vDateMin) + RowNo() - 1) AS TempDate
      AUTOGENERATE 1
      WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

      MasterCalendar:
      LOAD
      TempDate AS CalendarDate,
      Day(TempDate) AS CalendarDay,
      Week(TempDate) AS CalendarWeek,
      Weekday(TempDate) AS WeekDay,
      Month(TempDate) AS CalendarMonth,
      Year(TempDate) AS CalendarYear,
      'Q' & Ceil(Month(TempDate)/3) AS CalendarQuarter,
      Month(TempDate) & '-' & Year(TempDate) AS CalendarMonthAndYear,
      Week (TempDate) & '-'& Year (TempDate) as CalendarWeekAndYear,
      Year2Date(TempDate, 0, 1, $(vToday))*-1 AS CurYTDFlag,
      Year2Date(TempDate,-1, 1, $(vToday))*-1 AS LastYTDFlag,
      YearToDate(TempDate, 0, Ceil(Month(Today())/3) * 3 - 2 ) as CurQTDFlag,
      YearToDate(TempDate, -1, Ceil(Month(Today())/3) * 3 - 2 ) as LastQTDFlag,
      Year2Date(TempDate, 0, Month(Today())) as CurMTDFlag,
      Year2Date(TempDate, -1, Month(Today())) as LastMTDFlag

      RESIDENT TempCalendar
      ORDER BY TempDate ASC;

      DROP TABLE TempCalendar;

      I would appreciate help, Kind regards,

      Beata