topic How to have a calendar? in QlikView

How to have a calendar?

Fri, 23 Mar 2012 07:59:07 GMT

Hi,

I have a field name: Requested Date, I want this field name to be the basis of my calendar.

Table:

Fields:

Requested No,

Requested Date

Would it be possible to create a calendar from which minimum date and maximum date is extracted from Requested Date?

Need help from you guys, thanks.

Best Regards,

Bill

How to have a calendar?

Fri, 23 Mar 2012 08:21:45 GMT

Here is the code, might exist some syntax error because I directly input code here, instead of QV

Time_Range:

Load

min([Requested Date]) as minDate,

max([Requested Date]) as maxDate

resident [your table name];

let v_min = FieldValue('minDate',1);

let v_max = FieldValue('maxDate',1);

for i = Year($(v_min)) to Year($(v_max)) //loop for year

for j = 1 to 12 //loop for month

let v_month = AddMonths(Date#($(i),'YYYY'), $(j)-1); //get a DATE variable, value= 1st day of that month

for k = 1 to day(MonthEnd($(v_month))) //loop for day, using day(monthend()) to get the largest day

//connect $i, $j, $k as a string, formatted as YYYY-MM-DD

let v_date_str = $(i) & '-' & $(j) & '-' & $(k);

let v_date = Date#($(v_date_str),'YYYY-MM-DD'); conver it as a date type

//load this variable into your calendar table

Calendar_table:

Load * inline [

Calendar_Date

$(v_date)

];

How to have a calendar?

Fri, 23 Mar 2012 08:31:23 GMT

Eeeee...

I made a mistake. Above code is "create calendar table from the beginning of min year to end of max year“.

Eg:

min date is 2010-08-02, max date is 2010-09-31

The rows in calendar table will be 2010-01-01 to 2010-12-31

So if you just need "date between min & max date", try below one:

Time_Range:

Load

min([Requested Date]) as minDate,

max([Requested Date]) as maxDate

resident [your table name];

let v_min = FieldValue('minDate',1);

let v_max = FieldValue('maxDate',1);

for i = $(v_min) to $(v_max) //loop for every day

//above row might convert value from date to int, so convert it back to date

let v_date = Date#($(i),'YYYY-MM-DD')

Calendar_table:

Load * inline [

Calendar_Date

$(v_date)

];

Same as above code, has not executed in QV so might exist syntax error, I guess you can fix it by your self.

BTW, I don't think it is necessary to create a calendar table. If your date in your fact table is not continously, try edit your chart (only workable for chart, instead of table) by this way:

1. properties

2. axes

3. enable "continuous"

How to have a calendar?

Fri, 23 Mar 2012 08:45:39 GMT

Thanks. I already did it! Would you mind, do you know how to get quarter?

How to have a calendar?

Fri, 23 Mar 2012 08:53:20 GMT

Hi,

I forgot to include, is this possible? To get the weeknumbers per month? rather than per year?

Example: I want the output like this

Month Week No.

Jan 1

Jan 2

Jan 3

Jan 4

Feb 1

Feb 2

Feb 3

Feb 4

Anyone can help? Or is this really possible?

Re: How to have a calendar?

Fri, 23 Mar 2012 08:56:43 GMT

Hi,

you can get quarter using 3 technique:

1. Inline load

2. Loading from excel

3. Using applymap() function

here is attached sample application:

Enjoy,

Regards,

Arun Goel

How to have a calendar?

Fri, 23 Mar 2012 11:00:13 GMT

Hi Arun, thanks for the info.