13 Replies Latest reply: Aug 3, 2011 4:52 AM by BlackRockS RSS

    Generating Dates

      Guys,

               I need to generate Dates.A list Box has years in it.

             

               Now based on the year selection I need to generate dates.Something like this..

       

              If I select 2009 then the dates I generate will be like

       

              1st Jan 2009,8 Jan 2009,15 Jan 2009,........31st Dec 2009.

       

              These dates  should be like dimensions in a bubble graph.

       

              No idea how to do do this.Please let me know if anybody has an idea.

       

      Regards

      Navin.G

        • Generating Dates
          Rahul Gupta

          Hey Hi,

           

          you can use the following code for the autogenerate dates at the back-end and then connect it via Year to tha Data Model:

           

          LET MinYear = year(today())-2;

          LET MaxYear = Year(today());

          LET MonthMin = 1;

          LET MonthMax = 12;

          LET DayMin = 1;

          LET DayMax =30;

           

          for Y = $(MinYear) to $(MaxYear)

          for M = $(MonthMin) to $(MonthMax)

          for D = $(DayMin) to $(DayMax)

           

          Calender:

          LOAD

                  $(Y) AS Year,

                  $(M) AS Month;

                  $(D) AS Day,

                  makedate($(Y),$(M),$(D)) as Date

          AUTOGENERATE(1) ;

           

          NEXT D

          NEXT M

          NEXT Y

           

          Hope this helps....

           

          Regards

            • Re: Generating Dates

              Rahul,

                        Thanks for your reply.Have a small doubt.

                  

                        I need to generate dates having a seven day gap from 1st Jan.

               

                       Something like 1st Jan 2009,7th Jan 2009.

               

              Regards

              Navin

                • Generating Dates
                  Rahul Gupta

                  Hi Navin,

                   

                  For that you may use:

                   

                  LET MinYear = year(today())-2;

                  LET MaxYear = Year(today());

                  LET MonthMin = 1;

                  LET MonthMax = 12;

                  LET DayMin = 1;

                  LET DayMax =30;

                   

                  for Y = $(MinYear) to $(MaxYear)

                  for M = $(MonthMin) to $(MonthMax)

                  for D = $(DayMin) to $(DayMax)

                   

                  Calender:

                  LOAD

                          $(Y) AS Year,

                          $(M) AS Month;

                          $(D) AS Day,

                          makedate($(Y),$(M),$(D)) as Date

                  AUTOGENERATE(1) ;

                   

                  D=$(D)+5;

                   

                  NEXT D

                  NEXT M

                  NEXT Y

                   

                  Hope this Helps...

                   

                  Regards

                  • Re: Generating Dates

                    Hi Rob and Rahul,

                                               Below is the excel sheet attched which has four columns--Name Location,Interval and Date.

                     

                                               What I need is there will be bubble chart which has the values on X axis like 1-7 Jan 2009,8-15 Jan 2009,16-23 Jan 2009...24-31 Dec 2009.

                     

                                               Basically it is a seven day gap values.

                     

                                               Now the value of the bubble will be the Sum of Intervals

                     

                                               For Example: the first two rows have dates 01/01/2009,04/01/2009 Should come under 1-7 Jan 2009 and the

                                                                    bubble value will be Sum(Intervals) of 01/01/2009,04/01/2009.

                     

                                               Please let me know if you have any doubts.

                     

                    Regards

                    Navin     

                • Generating Dates
                  Rob Wunderlich

                  Please describe more about what your application is. You can generate dates a week apart -- like the WeekStart date -- in your master calendar and use thosevalues  as a dimension.

                   

                  -Rob

                    • Re: Generating Dates

                      Hi,

                          Let me know how to generate Week Start Dates as Rob Suggested Please.

                       

                      Regards

                      Navin

                        • Generating Dates
                          Rob Wunderlich

                          Get a copy of the QV Cookbook from

                          http://robwunderlich.com/downloads/

                           

                          Take a look at the example "Generate date (calendar) table.". That includes some typical patterns for generating dates. Dates are an integer. Incrementing a date by seven days is done by adding 7. There are also functions like WeekStart() that will identify the first day of the week.

                           

                          -Rob

                            • Re: Generating Dates

                              Hi Guys,

                                            I will let you know what my actual requirement is...

                               

                                            I have columns like Job Date and PM Interval

                               

                                           Now I need to generate dates like 1st Jan 2009,10th Jan 2009,20th Jan 2009,....31st Dec 2009.

                                           Dates have to be generated at an interval of 10 Days.I can do this date generation now.

                               

                                          I need to show a bubble chart which has these above generated dates in the x-axis and Pm Interval

                                          in the y-axis.

                               

                                          Now, Suppose we have the foll data of Job Date and PM Interval 

                               

                                          Job Date       PmInterval

                                          2-Jan-2009      30

                                          4-Jan-2009      40

                                       

                                         Now there will be two bubbles between 1st Jan 2009 and 10th Jan 2009 and at intersection point

                                         of PMInterval 30 and 40.

                               

                                         Please guide me with this.

                               

                                       Regards

                                       Navin.G

                              • Re: Generating Dates

                                Hi Guys,

                                              I will let you know what my actual requirement is...

                                 

                                              I have columns like Job Date and PM Interval

                                 

                                             Now I need to generate dates like 1st Jan 2009,10th Jan 2009,20th Jan 2009,....31st Dec 2009.

                                             Dates have to be generated at an interval of 10 Days.I can do this date generation now.

                                 

                                            I need to show a bubble chart which has these above generated dates in the x-axis and Pm Interval

                                            in the y-axis.

                                 

                                            Now, Suppose we have the foll data of Job Date and PM Interval 

                                 

                                            Job Date       PmInterval

                                            2-Jan-2009      30

                                            4-Jan-2009      40

                                         

                                           Now there will be two bubbles between 1st Jan 2009 and 10th Jan 2009 and at intersection point

                                           of PMInterval 30 and 40.

                                 

                                           Please guide me with this.Let me know if you have any issues.

                                 

                                         Regards

                                         Navin.G

                            • Re: Generating Dates

                              Hi,

                                  Let me know how to generate Week Start Dates as Rob Suggested Please.

                               

                              Regards

                              Navin

                                • Generating Dates

                                  Hey,

                                   

                                  I think this script will work

                                   

                                  let curr_dt=MakeDate(2009,1,1);
                                  let lst_dt=MonthEnd(curr_dt);

                                  For d= '$(curr_dt)' To '$(lst_dt)'
                                  let md=MakeDate(2009,1,Day(d)+6);
                                  let dd=day(md)+1;
                                  let d=makedate(2009,1,dd);
                                  Load $(md) as Date;
                                  Next d

                              • Generating Dates

                                Hi Navin ,

                                 

                                You can use MakeDate() method to generate date.

                                 

                                Ex:- MakeDate(2009,1,1) returns 2009-01-01

                                further now you can change the format of returned date like 1st jan 2009.

                                 

                                For more help about date function use 'using help' option in help menu in your qlikview.