topic Re: Week Number from Date in QlikView

Week Number from Date

pooja821 — Thu, 26 Apr 2018 07:20:08 GMT

Hi,

I want to fetch week number from date(date format is DD-MMM-YYYY).

For Eg My date is 02-Oct -2017. Since this date lies in week 2 so i want the output to be 2.

Given condition is that my week start from Monday.

For e.g. on 01-Oct-2017, its Sunday so it would come under week 1 and on from Monday on wards my week number would 2 for that specific week.

Kindly help.

Re: Week Number from Date

Gysbert_Wassenaar — Thu, 26 Apr 2018 08:30:49 GMT

Personally I wouldn't bother trying and instead create the calendar in an excel file. Then load the dates and the weeks from excel file.

Re: Week Number from Date

pooja821 — Thu, 26 Apr 2018 09:06:32 GMT

How would we able to do that.

Its just i want to fetch Week number from date.

I have done it using expression :Ceil((Day(date(date#(Date,'DD-MMM-YYYY'),'DD')))/7) as WeekNumber in load editor, but now the problem i m facing is that i want the first day of week to start with Monday as i explained in my scenario.

Re: Week Number from Date

olivierrobin — Thu, 26 Apr 2018 09:20:35 GMT

hello

why don't you use the parameters ok week function? (see documentation)

week(timestamp [, first_week_day [, broken_weeks [, reference_day]]])

Return data type: integer

Re: Week Number from Date

pooja821 — Thu, 26 Apr 2018 09:29:03 GMT

can you pls elaborate a little?

Re: Week Number from Date

olivierrobin — Thu, 26 Apr 2018 09:29:03 GMT

say you column is named myDate

if you use the function week(MyDate,0), il will compute the week with monday as first day

have a look at the documentation on this function to see various parameters

Re: Week Number from Date

pooja821 — Thu, 26 Apr 2018 09:38:16 GMT

it basically giving the number of weeks before that specified date.

Still not helping.

Re: Week Number from Date

olivierrobin — Thu, 26 Apr 2018 09:43:13 GMT

it returns the week number of the date in the year

Re: Week Number from Date

pooja821 — Thu, 26 Apr 2018 10:16:49 GMT

Exactly..

it returns the week number of the date in the year but i want week of the date in the month.

Re: Week Number from Date

olivierrobin — Thu, 26 Apr 2018 11:20:17 GMT

try to calculate the week number of your date minus og the 1st day of the month

Re: Week Number from Date

maxgro — Thu, 26 Apr 2018 11:43:51 GMT

try the attachment, the script is

load

date(makedate(2018) + rowno() -1) as date

AutoGenerate 365;

load

currentweek - firstweek +1 as monthweek

;

load

date,

week(WeekStart(MonthStart(date))) as firstweek,

week(date) as currentweek

Resident t;

DROP Table t;

Re: Week Number from Date

pooja821 — Thu, 26 Apr 2018 12:00:01 GMT

i want it in qliksense.

Re: Week Number from Date

pooja821 — Thu, 26 Apr 2018 12:00:49 GMT

how would we do that. i.e subtracting number from date.

Re: Week Number from Date

maxgro — Thu, 26 Apr 2018 12:07:51 GMT

the script I posted works in QlikView and Qlik Sense

Re: Week Number from Date

d_prashanthredd — Fri, 27 Apr 2018 07:32:51 GMT

HiI Pooja,

Below is the script for what you are lookig for.. Before going through the script you need to set the variable value

i.e. SET FirstWeekDay=0; in your main tab.

Tab1:

LOAD

"Date", ceil(Day(date((Date)))/7) as MonthWiseWeek, Num(WeekDay(Date)) as DayNum, week(Date) as YearWiseWeekNo

FROM [lib://Test/Book1.xlsx]

(ooxml, embedded labels, table is Sheet1);

Inner Join

Weeks:

load week(Date) as YearWiseWeekNo, Month(Date) & ' - Week - ' & MonthWiseWeek as [Month-Week] Resident Tab1 where DayNum = 0;

drop Field MonthWiseWeek;

Note:

1. I'm not sure your data is having all days, for that you just generate all missing days if you miss any. I suggest you to use master calendar

2. Make sure that your 1st Week in Feb starts on 5th as it is monday and Feb 1-4 days fall in Jan's 5th Week.

Please let me know if you are looking for something else

Re: Week Number from Date

pooja821 — Fri, 27 Apr 2018 08:40:01 GMT

Hi Prashanth,

Your logic works but its restricted to year 2018 only but i want it to cover all year including 1900 and so on.

Re: Week Number from Date

pooja821 — Fri, 27 Apr 2018 08:54:31 GMT

I want month wise week number instead of Year wise Week Number.

Re: Week Number from Date

pooja821 — Fri, 27 Apr 2018 09:02:31 GMT

I have attached a excel which have that column named DOJ.

It contains all the dates

What i want is to calculate the week number of the each date.

for example i have a date 02-Oct-2017, so i want its week number to be 2.

As my week starts with monday so on 01-Oct-2017 i took it as Week 1 so on 02-Oct-2017 its week 2.

Re: Week Number from Date

maxgro — Fri, 27 Apr 2018 10:05:18 GMT

Result with data from your excel, using the script below

Script

// load data from your excel

LOAD Month, DOJ

FROM book.xlsx (ooxml, embedded labels, table is [New Hires]);

// calculate the monthweek

load *,

currentweek - firstweek +1 as monthweek;

load

Month,

DOJ,

week(WeekStart(MonthStart(DOJ))) as firstweek,

week(DOJ) as currentweek

Resident t;

DROP Table t;

Re: Week Number from Date

d_prashanthredd — Fri, 27 Apr 2018 10:19:06 GMT

You mean, a week may contain 1 day as if the date 2nd is of Monday.

Ok.. Will try