Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
MarcoWedel

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
jonathandienst
Partner - Champion III
Partner - Champion III

>>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
Anonymous
Not applicable
Author

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.

MarcoWedel

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

Anonymous
Not applicable
Author

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?

MarcoWedel

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

Anonymous
Not applicable
Author

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).

MarcoWedel

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.

Anonymous
Not applicable
Author

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.