Creating a Calendar

[#1]

Hi All,

(I am using Qlikview10(unlicensed).

I need to create a calendar which should contain year, period, month, week, startdate, Enddate. I have managed to create Year, Month, Week, Day individually.

But How can I create a period or month. I have used this query.

Calendar:

LOAD

date(yearstart(Date),'YYYY') as Year,

date(monthstart(Date),'MMM YY') as Month,

weekday(Date) as WeekDay,

day(Date) as Day;

LOAD date(recno()+date#('20021231','YYYYMMDD')) as Date

AUTOGENERATE today()-date#('20021231','YYYYMMDD')

Please find attached of the excel-sheet. In this excel sheet you can see there is year, week, startdate and enddate. Can I able to create month and period from this.

Please suggest.

[its_anandrjs]

Hi,

Do some changes in script like

Calendar:

LOAD

date(yearstart(Date),'YYYY') as Year,

Month(Date) as Month,

weekday(Date) as WeekDay,

day(Date) as Day;

LOAD date(recno()+date#('20021231','YYYYMMDD')) as Date

AUTOGENERATE today()-date#('20021231','YYYYMMDD')

Change this line

Month(Date) as Month,

in place of

date(monthstart(Date),'MMM YY') as Month,

And i want to know what is meaning of period .

Rgds

Anand

[#3]

Hello Anand,

Thanks for the earliest response.

Period means Quarter(Jan,Feb,March).

I am completely new for Qlikview so don't have much idea on dates.

Thanks,

Sandeepa

[Anonymous]

Take a look at the atteched file.

It also contains a macro to create a data range.

Hope it is usefull for you.

[#5]

Hello Dennis,

Thanks for the response.

The Qlikview I have installed my machine is not licensed one. So I can't able to open it.

Please send me the script, so that I can refer it.

[Anonymous]

Based on Startdate from your Excelfile it should be something like the atteched file.

In this way the Year and Week also will be generated based on the Startdate.
You are ofcourse free to use te Week and Year field from the Excel file.

[Anonymous · Accepted solution]

SET ThousandSep='.';

SET DecimalSep=',';

SET MoneyThousandSep='.';

SET MoneyDecimalSep=',';

SET MoneyFormat='€ #.##0,00;€ -#.##0,00';

SET TimeFormat='h:mm:ss';

SET DateFormat='D-M-YYYY';

SET TimestampFormat='D-M-YYYY h:mm:ss[.fff]';

SET MonthNames='jan;feb;mrt;apr;mei;jun;jul;aug;sep;okt;nov;dec';

SET DayNames='ma;di;wo;do;vr;za;zo';

Data:

LOAD //Year,

    //Week,

       [Start Date] as datum,

     [Start Date],

     [End date]

FROM

[..\..\Tests\Book1.xlsx]

(ooxml, embedded labels, table is ImsCalendar);

StartKalender:

LOAD

          Date(Floor(datum)) as datum

Resident Data;

Master_Calendar:

LET varMinDate = num(peek('datum' , 0 , 'StartKalender'));

LET varMaxDate = num(peek('datum' , -1 , 'StartKalender'));

LET vToday=num(today());

LET MinDate = peek('datum' , 0 , 'StartKalender');

LET MaxDate = peek('datum' , -1 , 'StartKalender');

LET vstart=date(0);

Temp_Calendar:

LOAD $(varMinDate) + rowno()-1                                         AS Num,

date($(varMinDate) + rowno()-1)                               AS TempDate

AUTOGENERATE $(varMaxDate)-$(varMinDate) +1;

Master_Calendar:

LOAD

TempDate,

TempDate                                                                                           AS datum,

week(TempDate)                                                                                 AS Week,

year(TempDate)                                                                                 AS Year,

month(TempDate)                                                                       AS Month,

day(TempDate)                                                                                 AS Day,

weekday(TempDate)                                                                       AS WeekDay,

'Q' & ceil(month(TempDate) / 3)                               AS Quarter,

date(monthstart(TempDate), 'MMM-YYYY')                     AS MonthYear,

date(monthstart(TempDate), 'YYYY-MM')                     AS YearMonth,

week(TempDate) & '-' & year(TempDate)                     AS WeekYear,

inyeartodate(TempDate, $(vToday),0)*-1                     AS CurYTDFlag,

inyeartodate(TempDate,$(vToday),-1) * -1           AS LastYTDFlag

RESIDENT [Temp_Calendar]

ORDER BY TempDate asc;

DROP TABLE Temp_Calendar,StartKalender;

[its_anandrjs]

Hi,

Ok understood add quarter syntax below to add the quarter

like

Q' & ceil(month(Date) / 3)   AS Quarter,

Calendar:

LOAD

date(yearstart(Date),'YYYY') as Year,

Month(Date) as Month,

Q' & ceil(month(Date) / 3)   AS Quarter,

weekday(Date) as WeekDay,

day(Date) as Day;

LOAD date(recno()+date#('20021231','YYYYMMDD')) as Date

AUTOGENERATE today()-date#('20021231','YYYYMMDD')

Let me know about this

HTH

Rgds

Anand

[#9]

It's Perfect.

That's what I want.

Appreciated.

Thanks a lot.

[its_anandrjs]

Hi,

To create a calender follow this link

http://community.qlik.com/qlikviews/1001

Rgds

Anand