Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

berryandcherry6
Contributor II

Creation of dates from particular date to till today.

In my task i want to load a script which has to create dates. Date should start from 01/01/2012 and end till today. For each date i need to find weekend, year, weekday of a date.

How could i achieve this.Any kind of help is appreciated.

Tags (2)
1 Solution

Accepted Solutions

Re: Creation of dates from particular date to till today.

Try this to create dates:

LOAD Date(MakeDate(2012, 1, 1) + RecNo() - 1) as Date

AutoGenerate Today() - MakeDate(2012, 1, 1) + RecNo() + 1;

16 Replies

Re: Creation of dates from particular date to till today.

Try this to create dates:

LOAD Date(MakeDate(2012, 1, 1) + RecNo() - 1) as Date

AutoGenerate Today() - MakeDate(2012, 1, 1) + RecNo() + 1;

ramoncova06
Valued Contributor III

Re: Creation of dates from particular date to till today.

you can use a master calendar

The Master Calendar

Creating A Master Calendar

berryandcherry6
Contributor II

Re: Creation of dates from particular date to till today.

Hi stalwar1

Thanks for reply,

Here Its creating only january month dates of every year till 2016. I need to display all 12 months of every year till 2016.

Regards,

Supriya

berryandcherry6
Contributor II

Re: Creation of dates from particular date to till today.

Hi ramoncova06,

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

Regards,

Supriya

Not applicable

Re: Creation of dates from particular date to till today.

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());

DateField:

LOAD

  $(varMinDate) + RowNo()-1 AS Num,

  date($(varMinDate) + rowno() -1) AS TempDate,

  rowno()

  AUTOGENERATE

  $(varMaxDate) - $(varMinDate) +1;

//Master Calendar

MasterCalendar:

LOAD

   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

Resident DateField

Order By TempDate ASC;

DROP TABLE DateField;

Re: Creation of dates from particular date to till today.

I am seeing all dates. Check the attached out

ramoncova06
Valued Contributor III

Re: Creation of dates from particular date to till today.

here goes what I normally use 

LET vMinDate = '01/01/2012'

   LET vMaxDate = Today();

CalendarTemp:

LOAD $(vMinDate) + (RecNo()) + (IterNo()-1) AS AddedDate

AUTOGENERATE 1

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

Calendar:

load

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]

Resident CalendarTemp;

drop table CalendarTemp;

berryandcherry6
Contributor II

Re: Creation of dates from particular date to till today.

Hi raul.marquez,

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.

Regards,

Supriya

berryandcherry6
Contributor II

Re: Creation of dates from particular date to till today.

It showning me lines fetched is 0,and no values are being disaplyed. I have attached snapshot please look into that.Capture.PNG

Community Browser