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

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

subin6600 — Fri, 20 Apr 2012 10:26:28 GMT

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

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

CELAMBARASAN — Fri, 20 Apr 2012 10:32:26 GMT

Hi,

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

Celambarasan

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

subin6600 — Fri, 20 Apr 2012 10:36:42 GMT

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

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

CELAMBARASAN — Fri, 20 Apr 2012 10:41:23 GMT

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

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

subin6600 — Fri, 20 Apr 2012 10:52:26 GMT

I have only week number and year

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

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

CELAMBARASAN — Fri, 20 Apr 2012 11:08:33 GMT

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

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

Fri, 20 Apr 2012 11:17:22 GMT

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

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

subin6600 — Fri, 20 Apr 2012 11:28:11 GMT

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.

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

Fri, 20 Apr 2012 11:35:44 GMT

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

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

CELAMBARASAN — Fri, 20 Apr 2012 11:36:38 GMT

Hi,

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

Celambarasan

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

subin6600 — Fri, 20 Apr 2012 11:53:00 GMT

Thanks guys it worked well.

Monthname(MakeWeekDate(Year, week) ) ihave used.