12 Replies Latest reply: Aug 11, 2014 5:04 AM by Tresesco B RSS

    hi

      i have a date column and want to extract quarter information from this. The date is is the YYYYMMDD format.

       

      thanks

        • Re: hi
          Tresesco B

          Try like:

               Load

                        'Q' & Ceil(Month(Date#(YourDateField, 'YYYYMMDD'))/3) as Quarter

            • Re: hi

              Thanks, its working fine.

               

              I have one more requirement. I want set Nov as my first month so that if I select Q1, I should see, Nov, Dec and Jan month information.

               

              Regards,

              Viresh

                • Re: hi
                  AVIRAL NAG

                  Try this to create Quarter:

                   

                  'Q'&Ceil(Num(Month(AddMonths(Date,-10)))/3)         as [Fiscal Quarter]

                   

                  It will give you the result:

                  Q1 - Nov, Dec, Jan

                  Q2 - Feb, Mar, Apr

                  Q3 - May, Jun, Jul

                  Q4 - Aug, Sep, Oct

                   

                  See this Post:

                  http://community.qlik.com/thread/108327

                   

                  Regards

                  Aviral Nag

                  • Re: hi
                    Tresesco B

                    Then you have to recalculate your month field preferably using preceding load like:

                     

                    Load

                                  'Q' & Ceil(fMonth/3) as Quarter;

                    Load

                              Mod(Month(Date#(YourDateField, 'YYYYMMDD')) -11, 12)+1        as fMonth

                    From <>;

                      • Re: hi

                        I tried this but in the Fmonth list box I am getting numbers from 1 to 12.  Is it possible to show the month name instead of numbers?

                          • Re: hi
                            AVIRAL NAG

                            Have you tried my logic posted above.

                            It will work.

                             

                            See this:

                            Try this to create Quarter:

                             

                            'Q'&Ceil(Num(Month(AddMonths(Date,-10)))/3)         as [Fiscal Quarter]

                             

                            It will give you the result:

                            Q1 - Nov, Dec, Jan

                            Q2 - Feb, Mar, Apr

                            Q3 - May, Jun, Jul

                            Q4 - Aug, Sep, Oct

                             

                            See this Post:

                            http://community.qlik.com/thread/108327

                             

                            Regards

                            Aviral Nag

                            • Re: hi
                              Tresesco B

                              Try like:

                              Load     *,

                                            'Q' & Ceil(fMonth/3) as Quarter;

                              Load

                                        Mod(Month(Date#(YourDateField, 'YYYYMMDD')) -11, 12)+1        as fMonth,

                                         Month(Date#(YourDateField, 'YYYYMMDD')) as Month

                              From <>;

                               

                              Here if you select Nov from Month field, it will associate '1' in the fMonth field.

                      • Re: hi
                        Manish Kachhia

                        Create below line in your master calendar or below the Date field...

                        'Q'&Ceil(Month(Date#(YourDateField,'YYYYMMDD'))/3) as Quarter

                        • Re: hi

                          Its working fine, Thank You.

                           

                          I have one more requirement. How to set Nov as my first month. So if i select Q1, i should see, Nov, Dec and Jan info.

                           

                          Regards,

                          Viresh

                          • Re: hi
                            Hannan Tariq

                            Try this:

                            date(makedate(left(Date_Field,4),mid(Date_Field,5,2),Right(Date_Field,2)),'DD-MM-YYYY')

                             

                            It will convert YYYYMMDD into DD-MM-YYYY and after that try applying ceil function.