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

[Anonymous]

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?

[jonathandienst]

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

[Anonymous]

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 · Accepted solution]

Hi,

maybe one solution could be something like:

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]

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]

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

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]

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.