Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
subin6600
Creator III
Creator III

Need Script: How to get weeks divided into months correctly?

I have a request were data is only having Week number. To associate it with calender i have taken a dummy calender and joined the week with the data's week Number.
But the challenge is when we select a month say April 2012 it has week 18 lying in both April & May and the data is shown in both the months and so wrong values or the value is higher than expected.

Is there any scirpt that you can help me with which would make the last week of a month to be for itself and not consider the same week for next month.

I.e, April 2012 would have week 18 and May would start with week 19 and so on with all other months.

And I want this to be dynamic and not hard coded as i have data for many years.

Thanks in advance.

Subin

1 Solution

Accepted Solutions
Not applicable

Okay well then you can use simply makeweekdate() function.

So use

Monthname(MakeWeekDate(Year, week) )

It will give you the month in which that week starts that way you will always have only one month for a week.

And now, you are good to go.

Hope this helps, let me know if any issue.

..

Ashutosh

View solution in original post

10 Replies
CELAMBARASAN
Partner - Champion
Partner - Champion

Hi,

     You data has only week number?no year or something else?

Celambarasan

subin6600
Creator III
Creator III
Author

have year as well. Like we have got two entries. One field for year & one field for the Week No.

CELAMBARASAN
Partner - Champion
Partner - Champion

Hi,

     Then to form a date use MakeWeekDate(Year,Week,day of week) Function

     Do you have day of week?

     any other issue in this?

Celambarasan

subin6600
Creator III
Creator III
Author

I have only week number and year

like

Week     Year     Amount               My Defined months accoridng to normal calender

9            2012     100                     Mar

10          2012     108                     Mar

11          2012     106                     Mar

12          2012     200                     Mar

13          2012     215                     Mar, Apr( Here i want Week 13 to be in March and not in april)

14          2012     216                     Apr

15          2012     217                     Apr

16          2012     213                     Apr

17          2012     218                     Apr

18          2012     211                     Apr, May(Like wise i want  Week 18 to be in April and not in May)

19          2012     215                     May

20          2012     212                     May

CELAMBARASAN
Partner - Champion
Partner - Champion

Hi,

     Not all months start on sunday(if week start as sunday) and ends in saturday(if week ends in saturday).

     If you change that then April has less than 30 days?

Celambarasan

Not applicable

Hi,

When first day of your first week starts. If it starts always on 1st Jan then it should not be much difficult.

If not then It can be difficult for you to get days properly.

..

Ashutosh

subin6600
Creator III
Creator III
Author

Its irrespective of the days. Its just that i dont want the value to be calculated twice in both the months. I need either in the previous month or in the next month. And My user has agreed to have it like in the previous month itself.

Not applicable

Okay well then you can use simply makeweekdate() function.

So use

Monthname(MakeWeekDate(Year, week) )

It will give you the month in which that week starts that way you will always have only one month for a week.

And now, you are good to go.

Hope this helps, let me know if any issue.

..

Ashutosh

CELAMBARASAN
Partner - Champion
Partner - Champion

Hi,

     How you created month field using Month(Date)?if so then use Month(WeekStart(Date))

Celambarasan