topic Re: How to turn week no into months, quarters and years? in QlikView

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

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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?

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

jonathandienst — Fri, 01 Dec 2017 11:45:05 GMT

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

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

Anonymous — Fri, 01 Dec 2017 15:30:00 GMT

Hey Jonathan,

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

WEEK_NO

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.

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

MarcoWedel — Fri, 01 Dec 2017 23:22:51 GMT

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

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

Anonymous — Fri, 01 Dec 2017 23:58:40 GMT

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?

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

MarcoWedel — Sat, 02 Dec 2017 00:39:32 GMT

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

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

Anonymous — Sat, 02 Dec 2017 00:50:56 GMT

Hi,

When I tried the one you provided me:

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;

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

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

MarcoWedel — Sat, 02 Dec 2017 02:22:05 GMT

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.

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

Anonymous — Sat, 02 Dec 2017 02:32:37 GMT

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.