Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
lakshmibi
Contributor
Contributor

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

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..

6 Replies
krishna_2644
Specialist III
Specialist III

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
Luminary Alumni
Luminary Alumni

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
Master
Master

let vMin = num(Today());

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

Calendar:

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

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

vishalarote
Partner
Partner

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
Contributor II
Contributor II

Hey Jimmy,

 

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