4 Replies Latest reply: Oct 10, 2011 5:49 AM by Aissam Boumejjane RSS

    linking month with 'special' Quarter

    Aissam Boumejjane

      Hi Guys,

       

      sorry i'm still a newbie..

       

      I'm trying to link Quarter and month together. The quarter is from a different table file (I had to create an extra excel sheet to link month and quarter because a customer counts quarter differently, (see below), but that shoudn't be a problem since the table file also has month names and thus links the two tables together.

       

      Feb till Apr       -   Q1

      May till Jul       -   Q2

      Aug till Okt      -   Q3 

      Nov till Jan       -   Q4

       

      screenshot2.JPG

       

      Script:


      MasterFile:

      LOAD

             Date(Date) as Date,

           Year(Date)as Year,

           Month(Date) as Month,

      FROM

      a.xls

       

      Calender:

      LOAD

           Month(Month) as Month,

           Quarter

      FROM

      x.xls

       

       

      When I look at my script, it seems correct. But there still no link. If I click on aug I should see Q3. but that doesn't work.

       

      What am I doing wrong?

       

      Cheers!

        • Re: linking month with 'special' Quarter
          Sokkorn Cheav

          Hi,Try to load full calendar like this

          [Calendar]:
          LOAD 
           [DateField],
           DAY([DateField])                                                        AS [Day],
           WEEKDAY([DateField])                                              AS [WeekDay],
           WEEKDAY([DateField]) &' - '& DAY([DateField])       AS [Day-WeekDay],
           
           WEEK([DateField])                                                     AS [Week],
           'W' & WEEK([DateField]) &' - '& YEAR([DateField])   AS [Week-Year],
           
           NUM(MONTH([DateField]))                                        AS [MonthNum],
           MONTH([DateField])                                                   AS [Month],
           MONTH([DateField]) &' - '& YEAR([DateField])          AS [Month-Year],
           
           'Q' & CEIL(MONTH([DateField])/3)                             AS [Quarter],
           'Q' & CEIL(MONTH([DateField])/3) &' - '& YEAR([DateField]) AS [Quarter-Year],
           
           YEAR(DATE([DateField]))                                          AS [Year]
          FROM tblName;
          


          Let me know if this one help you.

           

          Regards,

          Sokkorn

            • Re: linking month with 'special' Quarter
              Aissam Boumejjane

              Hi Sokkorn

               

              Thanks for your help!! And sorry for the late response. Loading a full calendar works. However, I still need to find a way to group aug, sept, okt as a third quarter.  Normally the third quarter should be juli, aug and sept. Unfortunately the customers wants it differently, that’s why I need to find a solution.

               

               

               

              Thanks in advance!!!

               

               

               

              isam

            • linking month with 'special' Quarter
              Jonathan Dienst

              Hi

               

              Have you checked that the two load statements are returning equivalent data. There are serveral reasons why they may not be, including:

               

              • Non-intersection between the source dates
              • Different formats for the dates

               

              Be careful of using Month to link these tables - this will link January 2011 and January 2010 to the same quarter. The common practice is to link on Date and create a master calendar along the lines of the previous post.

               

              Hope this helps

              Jonathan