How to have a calendar?

[#1]

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

[#2]

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)

               ];

          next

     next

next

[#3]

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)

               ];

next

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"

[#4]

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

[#5]

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?

[#6]

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

[#7]

Hi Arun, thanks for the info.