Thanks for your reply, My requirement is to generate date/calender from given date to till today.
Yes i went through link you have given but that is for mapping of tables(which is like joins in SQL),but how could i apply this concept to create dates.If you provide some code or sample regarding this will be really helpfull
here goes what I normally use
LET vMinDate = '01/01/2012'
LET vMaxDate = Today();
LOAD $(vMinDate) + (RecNo()) + (IterNo()-1) AS AddedDate
WHILE Num($(vMinDate) + IterNo() -1) < Num($(vMaxDate));
date(AddedDate) as [Calendar Date],
Month(AddedDate) as [Calendar Month],
Year(AddedDate) as [Calendar Year],
WeekEnd(AddedDate) as [Calendar Week Ending],
weekyear(AddedDate) as [Calendar WeekYear#],
QuarterName(AddedDate) as [Calendar Year Quarter],
date(floor(MonthEnd(AddedDate))) as [Calendar Month End],
date(floor(MonthEnd(AddedDate,-1))) as [Previous Calendar Month End],
date(floor(MonthStart(AddedDate))) as [Calendar Month Start],
date(floor(MonthStart(AddedDate,-1))) as [Previous Calendar Month Start],
'Q' & Ceil(Month(AddedDate)/3) as [Calendar Quarter]
drop table CalendarTemp;
Try with this one you just need to set your date on the variables and on the first field of the table MasterCalendar.
Hope this work for you.
LET varMinDate = NUM(Peek('YourDate',0,'YourTable'));
LET varMaxDate = NUM(Peek('YourDate',-1,'YourTable'));
LET varToday = Num(today());
$(varMinDate) + RowNo()-1 AS Num,
date($(varMinDate) + rowno() -1) AS TempDate,
$(varMaxDate) - $(varMinDate) +1;
TempDate AS YourDate,
Week(TempDate) AS Week,
Day(TempDate) AS Day,
Year(TempDate) AS Year,
Month(TempDate) AS Month,
WeekDay(TempDate) AS WeekDay,
'Q' & Ceil(Month(TempDate)/3) AS Quarter
Order By TempDate ASC;
DROP TABLE DateField;
Thankyou for your reply,
In your answer you are mentioning to give date and tablename, why should i give tablename ,as i want to create list of dates ex: from 01/01/2012 to today().Date list should be created without depending on any table.
As i am newbie in qliksense,I have some doubths Could you give me reason why,
1.) its better with Num around MakeDate() and what made starting date change from 27-06-1894 to 01-01-2012.
2.) $(vDate) surrounding with ' made starting date change from 27-06-1894 to 01-01-2012 .
Your reasons will be helpful to me to understand better.
I am not sure why MakeDate() alone was not enough, but it seems that MakeDate() alone saved the date as an expression rather than date. For example, when you sent 01-01-2012 to the Date($(vDate) + iterno()-1) as Date, the vDate was evaluated as 1-1-2012 = -2012 -> 27-06-1894
When we use Num(MakeDate()), then vDate = 40909, which is just a number representation of 01-01-2014. When we used single quotes around our variable vDate we not longer did the math to convert 1-1-2012 = -2012, but we simply used '01-01-2012' which was correctly read as date because of the environmental variable set at the beginning of the script.
All of this is confusing, but to cut the long story short I would always recommend using a date in number format which is easy to convert to date whenever you want using Date() function.
I hope this will be helpful.