Qlik Community

QlikView Documents

Documents for QlikView related information.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More

Calendar.txt

juan_escobar
Contributor II

Calendar.txt

Hi guys,

I've been asking a lot of questions lately and I thought that I might make a little contribution to the community with an issue that mos of us have been dealing with.

I'm attaching a .txt file with a Master Calendar that I've been working on. There are thing taken from here and from there so it isn't 100% of my own.

Besides I have to thanks all of the people that have helped me here in the Community.

You can load this calendar using an "Include" function like this:

$(Include=C:\Path\Calendario.txt);

I hope this could be useful!

Thank you again!

Attachments
Comments
juan_escobar
Contributor II

I almost forget, the script has an explanation in spanish, if someone want to translate it, feel free to do it!

0 Likes
Partner
Partner

//FISCAL YEAR CALENDER: YEAR STARTS WITH APR MONTH

Let STARTDATE = Num(MakeDate(2012, 4));
Let ENDDATE = num(Floor(monthend(Today())));
SET DateFormat='DD/MM/YYYY';
[TEMP_DATE]:
LOAD
    Date($(STARTDATE) + IterNo() - 1, '$(DateFormat)') as Date AUTOGENERATE 1
    WHILE $(STARTDATE)+IterNo()-1<= $(ENDDATE);

[DATETABLE]:
LOAD
Date,
Year(Date) as ONLYYEAR,
YearName(Date,0,4) as YEAR, //YEAR STARTS WITH APR

//YearName(Date,0,1) as YEAR, //YEAR STARTS WITH JAN
Month(Date) as MONTH,
num(Month(Date)) as MONTHID,
Week(Date) as WEEK,
WeekDay(Date) as WEEKDAY,
Day(Date) as DAY,
Year(Date) & right('00' & week(Date),2) as YYYYWW,
Year(Date) & right('00' & num(Month(Date)),2) as YYYYMM,
Year(Date) & ceil(num(Month(Date))/3) as YYYYK,
QuarterName(QuarterStart(Date,0,4)) as QuarterName
RESIDENT [TEMP_DATE];

DROP TABLE [TEMP_DATE];

DATETABLE_NEW:
LOAD *,
if(WildMatch(QuarterName,'Apr*'),'Q1',
if(WildMatch(QuarterName,'Jul*'),'Q2',
if(WildMatch(QuarterName,'Oct*'),'Q3',
if(WildMatch(QuarterName,'Jan*'),'Q4')))) as Quarter,
AutoNumber(QuarterName) as QID

Resident [DATETABLE];

DROP Table [DATETABLE];


EXIT Script;

0 Likes
Version history
Revision #:
1 of 1
Last update:
‎2014-01-15 02:24 PM
Updated by: