Solved: Re: Week Number from Date - Qlik Community

pooja821 · ‎2018-04-26

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.

	
		OSZAR »

maxgro · ‎2018-04-26

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;

	
		OSZAR »

View solution in original post

Gysbert_Wassenaar · ‎2018-04-26

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.

	
		OSZAR »

talk is cheap, supply exceeds demand

pooja821 · ‎2018-04-26

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.

	
		OSZAR »

olivierrobin · ‎2018-04-26

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

	
		OSZAR »

pooja821 · ‎2018-04-26

can you pls elaborate a little?

	
		OSZAR »

olivierrobin · ‎2018-04-26

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

	
		OSZAR »

pooja821 · ‎2018-04-26

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

Still not helping.

	
		OSZAR »

olivierrobin · ‎2018-04-26

it returns the week number of the date in the year

	
		OSZAR »

pooja821 · ‎2018-04-26

Exactly..

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

	
		OSZAR »

olivierrobin · ‎2018-04-26

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

	
		OSZAR »