how to create master calendar with dates today() and today() + 30

[Anonymous]

hi all,

I'm very new to qlikview, i have a requirement to create a master calendar with dates mindate=today() and maxdate=today() +30.need to get all dates b/w them in date column. i tried out some code but doesn't work.  can any one pls help. thanx in advance.

[shiveshsingh]

May be this

let vMin = num(Today());

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

Calendar:

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

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

[rubenmarin · Accepted solution]

Hi all, it can be simplified to:

Calendar:

LOAD Date(Today()+RecNo()-1) as Date

AutoGenerate 30; // Or '31' intead of '30'

[shiveshsingh]

Yes, simplified

[deepak_gupta]

Try this one

LET vStartDate = num(Today());;

LET vEndDate   = num(Today()+30);

LOAD Date('$(vStartDate)'+RecNo()-1) as [Link Date],

num(Date('$(vStartDate)'+RecNo()-1)) as [Link Date Number],

'Q' & ceil(month(AddMonths(Date('$(vStartDate)'+RecNo()-1),-3))/3) as [Link Quarter],

num(month(AddMonths(Date('$(vStartDate)'+RecNo()-1),-3))) as [Link Month Num],

  Month(Date('$(vStartDate)'+RecNo()-1)) as [Link Month]

AutoGenerate '$(vEndDate)'-'$(vStartDate)'+1;

[Anonymous]

Hi Ruben Marin,

     Thanx a lot for ur reply and the script worked for me..

[Anonymous]

hi Ruben,

   One small issue again,     i used the script u gave as

MasterCalendar:

LOAD Date(Today()+RecNo()-1) as Date,

Date(Today()+RecNo()-1)  AS MCDate

AutoGenerate 30;

and  i have other table columns as

B:

LOAD BId,     

     BDate AS Date,

FROM

[Data.xlsx]

(ooxml, embedded labels, table is B);

In B table i have BDate values as april month dates..

now, if i take Date in dimension as Month(MCDate) & Day(MCDate) to get May 14 ...like that

and in expression i took count(BId) in straight table

then i'm getting dates of april month dates but not may & june(since i took MasterCalendar dates from May 14th to Jun12th).............how can i get dates of may&june in my straight table. Please help..

thanx in advance..

[rubenmarin]

Hi, it's not clear what dates you want, you want to get dates that cover the excel range?, in that case load the data first and then create the calendar:

B:

LOAD BId,    

     BDate AS Date,

FROM

[Data.xlsx]

(ooxml, embedded labels, table is B);

Calendar:

LOAD

Date(MinDate+ IterNo()-1) as Date,

Date(MinDate+ IterNo()-1) as MCDate

While MinDate, IterNo()-1) <= MaxDate;

LOAD

Min(FieldValue('Date', RecNo())) as MinDate,

Max(FieldValue('Date', RecNo())) as MaxDate

AutoGenerate FieldValueCount('Date');