Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
qliklearnervir
Creator
Creator

Want to create a master calendar from today to max date

HI,

I want to create a master calendar from today to maxdate as per requirement .Below the code i am using ....but its not showing right week filter ...kindly suggest

LET vToday = Num(Today());

MinMax:

LOAD

  Min([Arrival Date]) as MinDate,

  Max([Arrival Date]) as MaxDate

RESIDENT booking_f;

//Let varMinDate = Num(Peek('MinDate',0,’MinMax’));

//Let varMinDate = Num(Peek($(vToday)));

Let varMaxDate = Num(Peek('MaxDate',0,’MinMax’));

TempCalendar:

Load

$(vToday) + Iterno() -1 as Num,

Date($(vToday) + Iterno() -1) as TempDate

Autogenerate 1 while $(vToday) + Iterno() -1 <= $(varMaxDate);

MasterCalendar:

Load

TempDate As [Arrival Date],

Week(TempDate) As Week,

weekday(TempDate) as WeekDay,

Year(TempDate) As Year,

Month(TempDate) As Month

resident TempCalendar

Order by TempDate ASC;

drop table TempCalendar;

8 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

Please define your week numbering rules.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
qliklearnervir
Creator
Creator
Author

kindly suggest where to define .....

ecolomer
Master II
Master II

See this example:

jonathandienst
Partner - Champion III
Partner - Champion III

How about posting it here so we can help you?

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
ecolomer
Master II
Master II

try week(TempDate)

qliklearnervir
Creator
Creator
Author

week(TempDate) i have already used.....but not giving me right output

kavita25
Partner - Specialist
Partner - Specialist

Hi,

Try the below master calender

let vMinDate = num(MakeDate(2014,04,01));

let vMaxDate = floor(Today());

let vDateToday = num(today());

//************** Temp Calender ******************//

TempCalender:

load

$(vMinDate) + rowno() - 1 as DateNumber,

date($(vMinDate) + RowNo() - 1) as Date

AutoGenerate

$(vMaxDate) - $(vMinDate) + 1;

Master_Calender:

    

load (Date) as Date,

      Date(Date,'DD/MM/YYYY') as DATE,

      Year(Date) as Year,

      Month(Date) as Month,

      day(Date) as Day,

      Week(Date) as Week,

    'Q' & (ceil(WildMatch(month(Date),'Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec','Jan','Feb','Mar')/3)) as Quarter

  

    Resident TempCalender where exists(Date);

   drop table TempCalender;

Hope it helps you!!

qliklearnervir
Creator
Creator
Author

Just describing my scenerio.....

I want to filter out the data according to Today's date  till max date.

suppose today is 12th august 2015 and i have data till jan 2016.

so my first week should be week comes in date 12th august and vice versa till jan 2016.

i think my requirement is clear for every one .

kindly suggest