Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

MonthToDate value on minus

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

Tags (3)