Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Talk to Experts Tuesday, Live Q&A, September 22: Moving from QlikView to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

How to turn week no into months, quarters and years?

Hey everyone,

I have a set of data that shows me only week no. I have 102 weeks. I want to turn them into months, quarters and years. The week starts Monday. Every month is 4 weeks. Every year is 48 weeks. How can I do it?

1 Solution

Accepted Solutions
Highlighted

Hi,

maybe one solution could be something like:

QlikCommunity_Thread_283643_Pic1.JPG

tabCalendar:

LOAD *,

    Mod(WEEK_NO-1,4)+1 as WeekOfMonth,

    Mod(Div(WEEK_NO-1,4),12)+1 as Month,

    Mod(Div(WEEK_NO-1,12),4)+1 as Quarter,

    Div(WEEK_NO-1,48)+1 as Year;

LOAD RecNo() as WEEK_NO

AutoGenerate 102;

hope this helps

regards

Marco

View solution in original post

8 Replies
Highlighted

>>Every month is 4 weeks. Every year is 48 weeks.

Are the weeks 7 days? If so, how are you handling the 'missing' 4 weeks in a year?

If they are not 7 days (Monday - Sunday), then you need to provide more information on how you defines a week.

Either way, I suggest you use a master calendar and associate the weeks with the year/quarter/month in that table.

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

Hey Jonathan,

The week is 7 days starts Mondy. I have only week numbers (from week no 1 to week number 102).

WEEK_NO

1

2

3

.

.

102

I don't have anything else. I don't' have year or day but it's fine if we can make up a starting year or starting a day if we need to. I have this data for learning purpose. Therefore, I can play with it. I tried master calendar but it didn't work.

Highlighted

Hi,

maybe one solution could be something like:

QlikCommunity_Thread_283643_Pic1.JPG

tabCalendar:

LOAD *,

    Mod(WEEK_NO-1,4)+1 as WeekOfMonth,

    Mod(Div(WEEK_NO-1,4),12)+1 as Month,

    Mod(Div(WEEK_NO-1,12),4)+1 as Quarter,

    Div(WEEK_NO-1,48)+1 as Year;

LOAD RecNo() as WEEK_NO

AutoGenerate 102;

hope this helps

regards

Marco

View solution in original post

Highlighted
Contributor II
Contributor II

Thanks, Marco.  How if I want to convert WEEK_NO to periods? Each period is a month. I have 102 weeks which makes 26 periods. I want it to be P1, P2, P3, ...., P26?


Then every quarter = 4 periods (Q1,Q2,...,Q7). Then every ear should be 4 quarters or 16 periods (Y1,Y2,and Y3).

SO I should have the following:

26 periods (P1, P2, P3, ...., P26)

7 quarters ( (Q1,Q2,...,Q7)

3 years (Y1,Y2,Y3).

Can I do that?

Highlighted

Hi,

should be the same principle, simply adjust the number of weeks per period, quarter and year accordingly.

Please close your thread if your question is answered:

Qlik Community Tip: Marking Replies as Correct or Helpful

Thanks

Regards

Marco

Highlighted
Contributor II
Contributor II

Hi,

When I tried the one you provided me:

  1. tabCalendar: 
  2. LOAD *, 
  3.     Mod(WEEK_NO-1,4)+1 as WeekOfMonth, 
  4.     Mod(Div(WEEK_NO-1,4),12)+1 as Month, 
  5.     Mod(Div(WEEK_NO-1,12),4)+1 as Quarter, 
  6.     Div(WEEK_NO-1,48)+1 as Year; 
  7. LOAD RecNo() as WEEK_NO 
  8. AutoGenerate 102; 

This is the result

Qlik.png

That's why I said I want it as following:

26 periods (P1, P2, P3, ...., P26)

7 quarters ( (Q1,Q2,...,Q7)

3 years (Y1,Y2,Y3).

Highlighted

So your requirements changed?

I have 102 weeks. I want to turn them into months, quarters and years. The week starts Monday. Every month is 4 weeks. Every year is 48 weeks.


Please post a table of those new fields as well as your initial weekno showing your expected result.

Highlighted
Contributor II
Contributor II

Thanks, Marco, Your responses were super helpful. I did some changes on "tabCalendar" you provided me, and the result is as I wanted. Thanks a lot.