creating calendar table with mindate as today() and maxdate as today()+30

[Anonymous]

hi all,

   I'm very new to qlikview, i have q requirement to create a master calendar table with dates b/w today() & today()+30..all dates b/w them should come in date column.

can anyone pls help..thanx in advance..

[Qrishna]

Master calendar is usually created based on the dates/date fields that you have some where in data model by calculating min/max in that date fields and using those min/max values we create master calendar with all other date related fields like month,mon-yr,Quarter,YTD flags etc.

In your case min date is today() and max date is today()+30.So do this in your script:

let vMinDate = num(today());

let vMaxDate = num(Today())+30;

let vDiff = $(vMaxDate)-$(vMinDate);

TempCalendar:

Load date($(vMinDate)+IterNo()-1) AS TempDate

AUTOGENERATE (1)

WHILE $(vMinDate)+IterNo()-1<= $(vMaxDate);

MasterCalendar:

Load

  TempDate AS [Submit Date],

  week(TempDate) As Week,

  Year(TempDate) As Year,

  Month(TempDate) As Month,

  Day(TempDate) As Day,

  Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,

  WeekDay(TempDate) as WeekDay

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

[MarcoWedel]

Hi,

another solution might be:

tabCalendar: 
LOAD *, 
    Day(Date) as Day, 
    WeekDay(Date) as WeekDay, 
    Week(Date) as Week, 
    WeekName(Date) as WeekName, 
    Month(Date) as Month, 
    MonthName(Date) as MonthName, 
    Dual('Q'&Ceil(Month(Date)/3),Ceil(Month(Date)/3)) as Quarter, 
    QuarterName(Date) as QuarterName, 
    Year(Date) as Year, 
    WeekYear(Date) as WeekYear;
LOAD Date(Today()+RecNo()-1) as Date
AutoGenerate 31;

hope this helps

regards

Marco

[jheasley]

Another alternative that i have found pretty easy across many apps is to use one of those scripts to create a calendar QVD that spans from some year in the past all the way to 2050.  then i can just pull the date range i need from that master calendar QVD in any given app using the min/max of a date filed as my limiters.

[shiveshsingh]

let vMin = num(Today());

let vMax = num(Today()+30);

Calendar:

load date($(vMin)+RowNo()-1) as Date

AutoGenerate($(vMax)-$(vMin));

[vishalarote]

Maybe it will help you.

it will show date current date to (+30 days).

load

Date(daystart(today())+RecNo()-1,'DD/MM/YYYY') as Date

AutoGenerate 30;

[robertbrooks]

Hey Jimmy,

Do you have any example of how you have done this? It would be much appreciated!