topic Re: Week Numbers for dates in QlikView

Week Numbers for dates

kkkumar82 — Thu, 24 Mar 2016 18:02:08 GMT

HI all,

Consider I have this following code in the edit script.

Let vDay = Num(today());

LOAD

RowNo() as Sno,

Date($(vDay) -RowNo() +1 ) as Day,

num(Rand() * 1000, ' #,##0.00' ) as Amount

AutoGenerate 365;

What I want in this code is a week number for each date based on month and year

Like for example

Sno,Day,Amount, WeeknoMonthly, weeknumberyearly

125,24-03-2016, 205, 4,14

Hope I am clear.

Thanks

Kiran Kumar

Re: Week Numbers for dates

sunny_talwar — Thu, 24 Mar 2016 18:09:01 GMT

I think for WeekNumberYearly you can try the Week() function. Not really sure what you can do for WeekNoMonthly

Re: Week Numbers for dates

swuehl — Thu, 24 Mar 2016 18:11:48 GMT

For week number monthly, try something like

Autonumber( Week(Day), MonthName(Day) )

Re: Week Numbers for dates

kkkumar82 — Thu, 24 Mar 2016 18:13:34 GMT

Hi sunny,

=Week(Date#('06-01-2016','DD-MM-YYYY')) is giving 1 , just curious to know

when I use first day of this year, 01-01-2016 my system is giving 53. can you elaborate on this.

Thanks

Kiran Kumar

Re: Week Numbers for dates

sunny_talwar — Thu, 24 Mar 2016 18:17:34 GMT

Check this link out:

BrokenWeeks ‒ QlikView

UPDATE: Set BrokenWeeks=1;

This will make Jan 1st as Week 1

Re: Week Numbers for dates

swuehl — Thu, 24 Mar 2016 18:19:55 GMT

Or for older versions

Redefining the Week Numbers

QlikView by default is using ISO week numbering, as mentioned in Henric's blog post.

Re: Week Numbers for dates

kkkumar82 — Thu, 24 Mar 2016 18:22:56 GMT

Sunny,

I tried with the following after setting the BrokenWeeks to 1 ,

=Week(Date#('01-01-2016','DD-MM-YYYY'))

but still getting 53.

thanks

kiran

Re: Week Numbers for dates

swuehl — Thu, 24 Mar 2016 18:27:22 GMT

I think you need also

Set ReferenceDay=1;

Re: Week Numbers for dates

MarcoWedel — Fri, 25 Mar 2016 02:15:34 GMT

Hi,

maybe helpful:

Week creation

regards

Marco

Re: Week Numbers for dates

kkkumar82 — Fri, 25 Mar 2016 05:55:26 GMT

Thanks all for your advises will come back shortly

Re: Week Numbers for dates

Kushal_Chawda — Fri, 25 Mar 2016 06:09:47 GMT

for week number Yearly see the below

Redefining the Week Numbers

for week number monthly see the below

Monthly week number !