# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
cancel
Showing results for
Did you mean:
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
MVP

Hi,

maybe one solution could be something like:

tabCalendar:

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;

AutoGenerate 102;

hope this helps

regards

Marco

8 Replies
Partner

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

MVP

Hi,

maybe one solution could be something like:

tabCalendar:

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;

AutoGenerate 102;

hope this helps

regards

Marco

Contributor II
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?

MVP

Hi,

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

Qlik Community Tip: Marking Replies as Correct or Helpful

Thanks

Regards

Marco

Contributor II
Author

Hi,

When I tried the one you provided me:

1. tabCalendar:
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;
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).

MVP