linking month with 'special' Quarter

[Anonymous]

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

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!

[Sokkorn]

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

[jonathandienst]

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

[Anonymous]

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

[Anonymous]

Hi Jonathan

Thanks for your help!! You are right!!! I shouldn’t be linking month with month. Thanks for the Eye-Opener!!

I think the reason why there was no link is because of the different date format.

Thanks!

iSam