topic Re: Week Number from Month in QlikView

Week Number from Month

Wed, 05 Oct 2016 09:26:03 GMT

Hi all, i need your expertice help.

It's possible to obtain the week numbers grouping by month from a date?

For example, today() is Month=10

I need to obtain one table with:

Week39

Week40

Week41

Week42

Week43

Week44

Is This possible?

Thanks in advenced!!

Re: Week Number from Month

sunny_talwar — Wed, 05 Oct 2016 09:27:21 GMT

So you want to know all the weeks that come in the month of October? Is that the goal? In the script?

Re: Week Number from Month

Wed, 05 Oct 2016 09:29:10 GMT

yep, but the source is today()

Re: Week Number from Month

sunny_talwar — Wed, 05 Oct 2016 09:36:40 GMT

May be something like this:

Table:

LOAD Date(MonthStart(Today()) + IterNo() - 1) as Date,

Week(MonthStart(Today()) + IterNo() - 1) as WeekNum

AutoGenerate 1

While IterNo() <= (Floor(MonthEnd(Today())) - Floor(MonthStart(Today())) + 1);

Update: Fixed the order of MonthEnd and MonthStart in the while statement

Re: Week Number from Month

Wed, 05 Oct 2016 09:41:20 GMT

Thanks Sunny , but does not work, return 0 Rows

Re: Week Number from Month

sunny_talwar — Wed, 05 Oct 2016 09:42:33 GMT

Sorry, I just updated my code:

Table:

LOAD Date(MonthStart(Today()) + IterNo() - 1) as Date,

Week(MonthStart(Today()) + IterNo() - 1) as WeekNum

AutoGenerate 1

While IterNo() <= (Floor(MonthEnd(Today())) - Floor(MonthStart(Today())) + 1);

Re: Week Number from Month

Wed, 05 Oct 2016 09:44:30 GMT

yeeeeeeeeeah!! you are the best!!

Many thanks!!! Works perfectly!!

Re: Week Number from Month

eromiyasimon — Wed, 05 Oct 2016 09:48:14 GMT

hi francisco,

you can get a particular week number between two different dates or you can take the current week number for this year,

i have added for current year.

here's the solution

=ceil((Today()-'01/01/2016')/7)

hope this helps

Re: Week Number from Month

Thu, 06 Oct 2016 06:37:13 GMT

Thanks Simon, the Sunny answer resolved mi issue