topic Creating a Calendar in QlikView

Creating a Calendar

Tue, 27 Sep 2011 08:14:33 GMT

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.

Creating a Calendar

its_anandrjs — Tue, 27 Sep 2011 08:18:50 GMT

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

Re: Creating a Calendar

Tue, 27 Sep 2011 08:27:24 GMT

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

Re: Creating a Calendar

Anonymous — Tue, 27 Sep 2011 08:29:12 GMT

Take a look at the atteched file.

It also contains a macro to create a data range.

Hope it is usefull for you.

Re: Creating a Calendar

Tue, 27 Sep 2011 08:35:31 GMT

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.

Re: Creating a Calendar

Anonymous — Tue, 27 Sep 2011 08:36:37 GMT

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.

Re: Creating a Calendar

Anonymous — Tue, 27 Sep 2011 08:37:41 GMT

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;

Creating a Calendar

its_anandrjs — Tue, 27 Sep 2011 09:01:00 GMT

Hi,

Ok understood add quarter syntax below to add the quarter

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

Re: Creating a Calendar

Tue, 27 Sep 2011 09:06:54 GMT

It's Perfect.

That's what I want.

Appreciated.

Thanks a lot.

Creating a Calendar

its_anandrjs — Tue, 27 Sep 2011 09:09:31 GMT

Hi,

To create a calender follow this link

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

Rgds

Anand

Re: Creating a Calendar

Tue, 27 Sep 2011 09:27:12 GMT

Hello Anand,

It worked.

I did not understand the logic of Q' & ceil(month(Date) / 3) AS Quarter .

Please explain.

Thanks.

Re: Creating a Calendar

Tue, 27 Sep 2011 09:28:32 GMT

Hi,

Can you please explain me why you have used

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

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

Thanks.

Re: Creating a Calendar

its_anandrjs — Tue, 27 Sep 2011 09:32:57 GMT

Hi,

Thanks, I explain you In a year we have 4 quarters so when we us month(Date) / 3 it divides into 4 parts but there is no Q word so we just concatedate it by this

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

Rgds

Anand

Creating a Calendar

Anonymous — Tue, 27 Sep 2011 10:04:37 GMT

CurYTDFlag creates a flag (1) that indicates if it is the current year.

LastYTDFlag creates a flag (1) that indicates if it is the previous year.

Select the fields Year, CurYTDFlag and LastYTDFlag to see how it works.

These flags can be used in many ways, for example in expressions like:

Sum({<CurYTDFlag={1}>} Sales) //Returns only the Sum of the Sales of the current year

Sum({<LastYTDFlag={1}>} Sales) //Returns only the Sum of the Sales of the previous year

Hope it's helpfull.

Dennis.

Re: Creating a Calendar

Tue, 27 Sep 2011 11:43:21 GMT

Thanks Anand..

I understood the logic...

Re: Creating a Calendar

Tue, 27 Sep 2011 11:48:07 GMT

Yeah.. I got you..

Thanks,

Sandeepa