topic Re: Master calender in QlikView

Master calender

Wed, 17 Jul 2013 08:44:50 GMT

I need master calender for 2013 year.

like Jan 26 to feb 25

feb 26 to mar 25

26 dec to Jan 25...

pls help me on this..

Re: Master calender

kaushiknsolanki — Wed, 17 Jul 2013 09:07:13 GMT

Hi,

What exactly you want.

In this case what will be the month and year?

Regards,

kaushik Solanki

Re: Master calender

Sokkorn — Wed, 17 Jul 2013 09:26:30 GMT

Hi Supraja,

Let try below script

Let varMinDate = Num(Date(Date#('01-Jan-2013','dd-MMM-yyyy')));
Let varMaxDate = Num(Date(Date#('31-Dec-2013','dd-MMM-yyyy')));

TempCalendar:
LOAD
   $(varMinDate) + Iterno()-1 As Num,
   Date($(varMinDate) + IterNo() - 1) AS TempDate
   AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

MasterCalendar:
Load
    
    TempDate,
    WEEK(TempDate)                AS [Weeks],
    MONTH(TempDate)               AS [Months],
    CEIL(MONTH(TempDate)/3)       AS [Quarters],
    YEAR(TempDate)                AS [Years]
Resident TempCalendar;

DROP Table TempCalendar;

Above script will generate calendar for full year 2013. And if you want to make any calendar base on your requirement, then change date in varMinDate and varMaxDate.

By the way, above script just an idea; while we don't understand what exactly you want. And if this one is the one you looking for, then help to close your post by click here.

Hope this help.

Regards,

Sokkorn

Re: Master calender

Wed, 17 Jul 2013 11:44:17 GMT

my query is i need master calender for billing dump.

that should be like, if i select January month it should take from 26th Jan to 25th feb

like that it should continue for whole year

finally if i select December it should take 26th Dec to 25 th jan..pls help on this..

Re: Master calender

kaushiknsolanki — Wed, 17 Jul 2013 11:54:10 GMT

Hi,

Try this.

Let vMin = num(makedate(2010,01,26));
Let vMax = Num(Today());

Temp:
Load Date($(vMin) +RowNo() -1) as DATE
AutoGenerate 1
While Date($(vMin) +RowNo() -1) <Date($(vMax));

Master_cal:
Load DATE,
Day(DATE) as DAY,
if(Day(DATE) >=26,Month(AddMonths(DATE,1)), Month(DATE)) as MONTH,
if(Day(DATE) >=26,Year(AddMonths(DATE,1)), Year(DATE))as YEAR
Resident Temp;

Drop table Temp;

Regards,

Kaushik Solanki