Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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..
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;
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
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.
let vMin = num(Today());
let vMax = num(Today()+30);
Calendar:
load date($(vMin)+RowNo()-1) as Date
AutoGenerate($(vMax)-$(vMin));
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;
Hey Jimmy,
Do you have any example of how you have done this? It would be much appreciated!