topic Re: Create a MasterCalendar with Month and Year in QlikView

Create a MasterCalendar with Month and Year

Anonymous — Mon, 02 Mar 2015 14:30:09 GMT

Hi,

I've a Startdate (2013-10-01) and Enddate (2015-01-01). On Load my calendar should be generate a Startdate like Oct 13 and an Enddate like Sep 15.

Regards Stefan

Nachricht geändert durch Stefan Fischer

Re: Create a MasterCalendar with Month and Year

Gysbert_Wassenaar — Mon, 02 Mar 2015 14:54:13 GMT

LET vStart = num(date#('2013-10-01','YYYY-MM-DD'));
LET vEnd =  num(date#('2015-09-01','YYYY-MM-DD'));

Calendar:
LOAD Month, Year(Month) as Year;
LOAD
    AddMonths($(vStart), iterno() -1) as Month
AUTOGENERATE1)
WHILEAddMonths($(vStart), iterno() -1) <= $(vEnd);

Or perhaps you want this:

LET vStart = num(date#('2013-10-01','YYYY-MM-DD'));
LET vEnd =  num(date#('2015-09-01','YYYY-MM-DD'));

Calendar:
LOAD $(vStart) as StartDate, $(vEnd) as EndDate
AUTOGENERATE(1);

Re: Create a MasterCalendar with Month and Year

Anonymous — Mon, 02 Mar 2015 15:13:01 GMT

Thats fine, but how could it ended on Sep 15?

Re: Create a MasterCalendar with Month and Year

Gysbert_Wassenaar — Mon, 02 Mar 2015 15:19:13 GMT

Sep 15 of which year?

Re: Create a MasterCalendar with Month and Year

Anonymous — Mon, 02 Mar 2015 15:20:31 GMT

Year of vEnd

Re: Create a MasterCalendar with Month and Year

Gysbert_Wassenaar — Mon, 02 Mar 2015 15:22:12 GMT

Change vEnd:

LET vStart = num(date#('2013-10-01','YYYY-MM-DD'));  
LET vEnd =  num(date#('2015-09-15','YYYY-MM-DD'));  
  
Calendar:  
LOAD $(vStart) as StartDate, $(vEnd) as EndDate  
AUTOGENERATE(1);

Re: Create a MasterCalendar with Month and Year

Anonymous — Mon, 02 Mar 2015 16:02:11 GMT

That's right, but for 2015-09-01 there is not data for load. I've problems to solve my question. Can you help pls.

Re: Create a MasterCalendar with Month and Year

Gysbert_Wassenaar — Mon, 02 Mar 2015 16:10:43 GMT

I'm afraid I don't have any idea anymore what you want. Can you please explain what you want in more detail?

Re: Create a MasterCalendar with Month and Year

Anonymous — Mon, 02 Mar 2015 16:17:45 GMT

I've add one sample. If i extract year in 2015-01-01, vEnd is dependent of last date. It is possible?

LET vEnd = num(date#(year(month)&'-09-15','YYYY-MM-DD'));

Re: Create a MasterCalendar with Month and Year

Gysbert_Wassenaar — Mon, 02 Mar 2015 16:28:09 GMT

LET vEnd = num(date#(year(month)&'-09-15','YYYY-MM-DD'));

The above gives no result because year(month) can't be calculated because 'month' is undefined. You would have to create a variable named month first.

Re: Create a MasterCalendar with Month and Year

Anonymous — Mon, 02 Mar 2015 16:44:54 GMT

Script will not working?

TempDate:
Load * Inline [
month
'2013-10-01',
'2015-09-15'
];

LET vStart = num(date#(month,'YYYY-MM-DD'));
LET vEnd = num(date#(year(month)&'-09-15','YYYY-MM-DD'));

Calendar:
LOAD Month, Year(Month) as Year;

//
LOAD
AddMonths($(vStart), iterno() -1) as Month
AUTOGENERATE(1)
WHILE AddMonths($(vStart), iterno() -1) <= $(vEnd);

MasterCalender:
LOAD
Date(Month, 'MM') as mon_MM,
Date(Month, 'MMM YY') as mon_MY
Resident Calendar

Re: Create a MasterCalendar with Month and Year

Gysbert_Wassenaar — Mon, 02 Mar 2015 16:51:36 GMT

That depends of your definition of 'working'. But most likely no, it will not work.

Re: Create a MasterCalendar with Month and Year

Anonymous — Mon, 02 Mar 2015 17:14:02 GMT

And what's wrong?

Re: Create a MasterCalendar with Month and Year

Gysbert_Wassenaar — Mon, 02 Mar 2015 17:21:57 GMT

What I said before. You didn't define the variable 'month'. Try using the peek function to get the value from the TempDate table instead.

TempDate:
Load * Inline [
month
'2013-10-01',
'2015-09-15'
];

LET vStart = num(date#(peek('month',0),'YYYY-MM-DD'));
LET vEnd = num(date#(peek('month',-1),'YYYY-MM-DD'));

Re: Create a MasterCalendar with Month and Year

Anonymous — Tue, 03 Mar 2015 13:30:42 GMT

Hi, now it works.

TempDate:
Load * Inline [
minDate, maxDate
2013-10-01, 2015-09-01];

Let vStart = num(date#(Fieldvalue('minDate',1),'YYYY-MM-DD'));
Let vEnd = num(date#(Fieldvalue('maxDate',1),'YYYY-MM-DD'));

DROP Table TempDate;

Calendar:
LOAD Date(Month,'YYYY-MM-DD')as Month, Year(Month) as Year;

LOAD
AddMonths($(vStart), iterno() -1) as Month
AUTOGENERATE(1)
WHILE AddMonths($(vStart), iterno() -1) <= $(vEnd);