Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
pooja821
Creator
Creator

Week Number from Date

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.

1 Solution

Accepted Solutions
maxgro
MVP
MVP

try the attachment, the script is

t:

load

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

AutoGenerate 365;

u:

load

     *,

     currentweek - firstweek +1 as monthweek

;

load

     date,

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

     week(date) as currentweek

Resident t;

DROP Table t;

View solution in original post

28 Replies
Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand
pooja821
Creator
Creator
Author

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.

olivierrobin
Specialist III
Specialist III

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

pooja821
Creator
Creator
Author

can you pls elaborate a little?

olivierrobin
Specialist III
Specialist III

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

pooja821
Creator
Creator
Author

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

Still not helping.

olivierrobin
Specialist III
Specialist III

it returns the week number of the date in the year

pooja821
Creator
Creator
Author

Exactly..

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

olivierrobin
Specialist III
Specialist III

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