6 Replies Latest reply: Mar 23, 2012 7:00 AM by Bill Ringer Salalima RSS

    How to have a calendar?

      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?

          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

            • How to have a calendar?

              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"