Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ivandrago
Creator II
Creator 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
Gysbert_Wassenaar

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

View solution in original post

4 Replies
Gysbert_Wassenaar

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
Anonymous
Not applicable

I prefer it shorter:

LET vStartDate = num(makedate(2011));

Gysbert_Wassenaar

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
Anonymous
Not applicable

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