Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Write Table now available in Qlik Cloud Analytics: Read Blog
cancel
Showing results for 
Search instead for 
Did you mean: 
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

0 Replies