Question - Master calander

Tue, 24 Nov 2015 10:36:29 GMT

I would like to set my MaxDate to 2015-09-30 (September 30th, 2015). In order to the script to only include transactions until this date. See below an extract of my Master Calander. How should I adjust the script in order to get the MaxDate to 2015-09-30?

Many thanks

MinMax:
LOAD Min( AccountDate) as MinDate,
Max( AccountDate) as MaxDate
Resident Trans;

LET vMinDate = Num( Peek( 'MinDate', 0, 'MinMax'));
LET vMaxDate = Num( Peek( 'MaxDate', 0, 'MinMax'));
LET vToday = $(vMaxDate);

// *** Temporary Calendar ***
TempCal:
LOAD
Date( $(vMinDate) + RowNo() - 1) as TempDate
AutoGenerate
$(vMaxDate) - $(vMinDate) + 1;

DROP Table MinMax;

// *** Master Calendar ***
MasterCalendar:
LOAD
TempDate as AccountDate,
Week(TempDate) as Week,
Year( TempDate) as Year,
Month( TempDate) as Month,
Day( TempDate) as Day,
WeekDay( TempDate) as WeekDay,
'Q' & Ceil( Month( TempDate) / 3) as Quarter,
Date( MonthStart( TempDate), 'MMM-YYYY') as MonthYear,
Week( TempDate) & '-' & Year( TempDate) as WeekYear,
InYearToDate( TempDate, $(vToday), 0) * - 1 as CurYTDFlag,
InYearToDate( TempDate, $(vToday), - 1) * - 1 as LastYTDFlag
Resident TempCal
Order By TempDate asc;

DROP Table TempCal;

Re: Question - Master calander

sunny_talwar — Tue, 24 Nov 2015 11:14:32 GMT

You just need to change this part:

MinMax:
LOAD Min( AccountDate) as MinDate,
MakeDate(2015, 9, 30) as MaxDate
Resident Trans;

Re: Question - Master calander

Anonymous — Tue, 24 Nov 2015 11:17:15 GMT

like this as well:

MinMax:
LOAD Min( AccountDate) as MinDate,
//Max( AccountDate) as MaxDate //Comment it
Resident Trans;

LET vMinDate = Num( Peek( 'MinDate', 0, 'MinMax'));
LET vMaxDate = date(MakeDate(2015, 9, 30),'DateFormat');
LET vToday = $(vMaxDate);

Hope this will help!!

topic Re: Question - Master calander in QlikView

Question - Master calander

Re: Question - Master calander

Re: Question - Master calander