Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

ivandrago
Contributor II

Calendar fixed start date

Hi,

I have the following calendar script, I want the StartDate to always be the 01/01/2011 but not shure how to do this?

[StartDate]:
LOAD min([InvoiceDate]) as Oldest 
FROM

(qvd);

//Calendar Range
LET vStartDate = If(peek('Oldest')<=monthstart(addmonths($(vToday),-13)),
      peek('Oldest')-1,
      monthstart(addmonths($(vToday),-13))-1);  // the -1 makes loop easier
LET vEndDate = num(yearend(today()));

SET vCompStartDate = monthstart(addmonths(today(),-14));
     

DROP TABLE [StartDate];


Calendar:
LOAD Date,
  year(Date) as Year,
  month(Date) as Month,
  monthstart(Date) as [Month Start],
  'W'&week(Date) as Week,
  month(Date)&'-'&Year(Date) as [Month Year],
  'Q'&Ceil(Month(Date)/3) as Quarter,
  QuarterName(Date) as [Quarter Name],
   day(Date) as Day,
  weekday(Date) as Weekday,
  weekday(Date)&' '&day(Date) as [Day of Week],
  if(Date=$(vToday), 1) as [CD Flag],
// if(Date=($(vToday)-1),1)as [PD Flag],
  if(Date=($(vToday)-1),1)as [PD Flag 2],
  if(year(Date)=year($(vToday)),1) as [CY Flag],
  if(year(Date)=year(addyears($(vToday),-1)),1)as [PY Flag],
  If((Date>=YearStart($(vToday)) and Date<=$(vToday)), 1) as [CYTD Flag],
  If((Date>=YearStart($(vOneYearAgo)) and Date<=$(vOneYearAgo)), 1) as [PYTD Flag],
  if(QuarterName(Date)=QuarterName($(vToday)),1) as [CQ Flag],                
  If((Date>=QuarterStart($(vToday)) and Date<=$(vToday)), 1) as [CQTD Flag],
  If((Date>=QuarterStart(AddMonths($(vToday),-3)) and Date<=AddMonths($(vToday),-3)), 1) as [PQTD Flag],      
  If((Date>=QuarterStart($(vOneYearAgo)) and Date<=$(vOneYearAgo)), 1) as [PYQTD Flag],
  If((Date>=MonthStart($(vToday)) and Date<=MonthEnd($(vToday))), 1) as [CM Flag],            
  If((Date>=MonthStart($(vToday)) and Date<=$(vToday)), 1) as [CMTD Flag],
  If((Date>$(vToday) and Date<=MonthEnd($(vToday))), 1) as [RCM Flag],             
  If((Date>=MonthStart(AddMonths($(vToday),-1)) and Date<=AddMonths($(vToday),-1)), 1) as [PMTD Flag],
  If((Date>=MonthStart(AddMonths($(vToday),-1)) and Date<=MonthEnd(AddMonths($(vToday),-1))), 1) as [PM Flag],
  If((Date>=MonthStart($(vOneYearAgo)) and Date<=$(vOneYearAgo)), 1) as [PYMTD Flag],
  If((Date>=MonthStart(AddMonths($(vToday),-3)) and Date<=MonthEnd(AddMonths($(vToday),-1))), 1) as [R3 Flag],    
  If((Date>=MonthStart(AddMonths($(vToday),-12)) and Date<=MonthEnd(AddMonths($(vToday),-1))), 1) as [R12 Flag],    
  If((Date>=MonthStart(AddMonths($(vOneYearAgo),-3)) and Date<=MonthEnd(AddMonths($(vOneYearAgo),-1))), 1) as [PY R3 Flag], 
  If((Date>=MonthStart(AddMonths($(vOneYearAgo),-12)) and Date<=MonthEnd(AddMonths($(vOneYearAgo),-1))), 1) as [PY R12 Flag], 
  If((Date>=WeekStart($(vToday)) and Date<=WeekEnd($(vToday))), 1) as [CW Flag],            
  If((Date>=WeekStart($(vToday)-7) and Date<=WeekEnd($(vToday)-7)), 1) as [PW Flag],           
  If((Date>=WeekStart($(vToday)) and Date<=$(vToday)), 1) as [CWTD Flag],              
  If((Date>$(vToday) and Date<=WeekEnd($(vToday))), 1) as [RCW Flag],
  If(Year(Date)>=year(addmonths(today(),-13)),Year(Date)) as [New Year];               
 
LOAD date($(vStartDate) + IterNo()) as Date
AUTOGENERATE 1
WHILE $(vStartDate) + IterNo() <= $(vEndDate);


[Analysis Periods]:
LOAD monthstart(addmonths($(vCompStartDate), 1*IterNo())) as [Month Start],
  IterNo() as [Analysis Period]
AUTOGENERATE 1
WHILE IterNo() <= 14;

1 Solution

Accepted Solutions

Re: Calendar fixed start date

Change

LET vStartDate = If(peek('Oldest')<=monthstart(addmonths($(vToday),-13)),

      peek('Oldest')-1,

      monthstart(addmonths($(vToday),-13))-1);  // the -1 makes loop easier


to


LET vStartDate = num(date#('01/01/2011','DD/MM/YYYY'));


talk is cheap, supply exceeds demand
4 Replies

Re: Calendar fixed start date

Change

LET vStartDate = If(peek('Oldest')<=monthstart(addmonths($(vToday),-13)),

      peek('Oldest')-1,

      monthstart(addmonths($(vToday),-13))-1);  // the -1 makes loop easier


to


LET vStartDate = num(date#('01/01/2011','DD/MM/YYYY'));


talk is cheap, supply exceeds demand
mov
Esteemed Contributor III

Re: Calendar fixed start date

I prefer it shorter:

LET vStartDate = num(makedate(2011));

Re: Calendar fixed start date

Me too, but this way the OP should be able understand how to use any start date, not just first of Januaries.


talk is cheap, supply exceeds demand
mov
Esteemed Contributor III

Re: Calendar fixed start date

Gysbert,

Can't disagree with this.  That's why I didn't use the shortest possible:

LET vStartDate = 40544; 

Anyway, the makedate() has its advantage - it doesn't depend on the date format.  So, this maybe a better way - has year, month, and day, and format-independent at the same time:

LET vStartDate = num(makedate(2011,1,1));

Regards,

Michael

Community Browser