Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

aboumejjane
Contributor

linking month with 'special' Quarter

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!

4 Replies
Sokkorn
Honored Contributor

Re: linking month with 'special' Quarter

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

MVP
MVP

linking month with 'special' Quarter

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
aboumejjane
Contributor

Re: linking month with 'special' Quarter

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

aboumejjane
Contributor

Re: linking month with 'special' Quarter

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