topic Calendar Start with 29th to 28th in QlikView

Calendar Start with 29th to 28th

neha_shirsath — Sat, 22 Mar 2014 11:14:17 GMT

Hi Community,

Need your Help for Calendar.

I need calendar like below condition for month.

July 01 - 28
Feb from 29 jan to 26 feb

March 27 feb to 28 March
June from 29 May to 30 junbe
Aug from 29 July to 28th August
Sept from 29 August to 28 sept

Continue all the other months in the format of Aug and sept .

I need master Calendar for above condition.

Thanks & Regards,

Neha

Re: Calendar Start with 29th to 28th

swuehl — Sat, 22 Mar 2014 11:49:18 GMT

Maybe like this:

Set DateFormat = 'MM/DD/YYYY';

//Just chose any leap year for mapping table creation (e.g. 2012)

MAP:

Mapping LOAD DayNumberOfYear(Date), NewMonth;

LOAD Date(StartDate+iterno()-1) as Date, NewMonth INLINE [

NewMonth, StartDate, EndDate

Feb, 01/29/2012, 02/26/2012

Mar, 02/27/2012, 03/28/2012

Apr, 03/29/2012, 04/28/2012

May, 04/29/2012, 05/28/2012

Jun, 05/29/2012, 06/30/2012

Jul, 07/01/2012, 07/28/2012

Aug, 07/29/2012, 08/28/2012

Sep, 08/29/2012, 09/28/2012

Oct, 09/29/2012, 10/28/2012

Nov, 10/29/2012, 11/28/2012

Dec, 11/29/2012, 12/28/2012

Jan, 12/29/2012, 01/28/2013

] while StartDate+IterNo()-1 <= EndDate;

MasterCalendar: //for years 2010 to 2014 in this example

LOAD Date,

Day(Date) as Day,

Month(Date) as Month,

Year(Date) as Year,

DayNumberOfYear(Date) as DNoY,

Applymap('MAP',DayNumberOfYear(Date)) as MappedMonth;

LOAD makedate(2010)+recno()-1 as Date AutoGenerate 366+3*365;

Re: Calendar Start with 29th to 28th

Mon, 09 Jun 2014 09:35:28 GMT

Hi Swuehl,

As per the logic calendar works fine with 29th to 28th but it gives result for MappedMonth field as in Jan,Feb,Mar ...etc. in field value.Its nothing but combination of Dec-2011 Jan-2012 in place of Jan.So I want rather than just Jan it will show "Jan-2012" in resulted field value of MappedMonth with combination of Dec-2011 Jan-2012. Is it possible?

Thanks & Regards,

Harshal

Re: Calendar Start with 29th to 28th

swuehl — Mon, 09 Jun 2014 10:19:00 GMT

Should be possible. Maybe using something like

...

MasterCalendar: //for years 2010 to 2014 in this example

LOAD *, MappedMonth &'-'& if(Month=12 and MappedMonth='Jan', Year+1,Year) as MappedMonthYear;

LOAD Date,

Day(Date) as Day,

Month(Date) as Month,

Year(Date) as Year,

DayNumberOfYear(Date) as DNoY,

Applymap('MAP',DayNumberOfYear(Date)) as MappedMonth;

LOAD makedate(2010)+recno()-1 as Date AutoGenerate 366+3*365;