Solved: Re: Week Number from Date - Page 3 - 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 »

d_prashanthredd · ‎2018-04-27

That was a sample excel..You can extract minumudate and max date values from your data. That you can do in calendar tab to make it simple.

	
		OSZAR »

pooja821 · ‎2018-04-27

Thanks Massimo.

	
		OSZAR »

cmorri1988 · ‎2018-04-27

LOAD

Date,

week(Date)- week(weekstart(monthstart(Date)))+1 as week_within_month

FROM...

	
		OSZAR »

pooja821 · ‎2018-05-04

Hi Massimo,

In the script used above, after computing the hiring week using your logic, M getting negative values for several dates.

Have attached the snapshot of the same.

Kindly help on this.

	
		OSZAR »

cmorri1988 · ‎2018-05-04

Hi Pooja, your currentweek and first week should not be so different, as one is week(WeekStart(MonthStart(DOJ))) and the other is week(DOJ) as currentweek. Please post your script. Thanks

	
		OSZAR »

pooja821 · ‎2018-05-04

this is my script

load *,

currentweek - firstweek +1 as Hiring week;

load

DOJ,

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

week(DOJ) as currentweek ,*;

	
		OSZAR »

maxgro · ‎2018-05-04

Pooja Goswami:

"

Hi Massimo,

In the script used above, after computing the hiring week using your logic, M getting negative values for several dates.

Have attached the snapshot of the same.

"

maybe this, the new field (without negative) is monthweek1

t:

load

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

AutoGenerate 365*50;

u:

load

*,

year(date) as year,

month(date) as month,

currentweek - firstweek +1 as monthweek,

if(floor(month(date))=1 and currentweek < firstweek, currentweek +1,

if(floor(month(date))=12 and currentweek < firstweek, previousweek + 1 - firstweek +1,

currentweek - firstweek +1))

as monthweek1

//floor(month(date))

;

load

date,

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

week(date) as currentweek,

week(date-7) as previousweek,

week(date+7) as nextweek

Resident t;

DROP Table t;

	
		OSZAR »

pooja821 · ‎2018-05-07

Hi Massimo,

The above script is working fine but as per my requirement, my week should start with Monday and should end on Sunday.

So if we take a case of 2nd Oct 2017, though it lies in week 1 but since its on Monday, then it would be counted in week 2, as 1st Oct 2017 has already been counted in week 1.

Kindly help on this and thanks a lot for your effort.

	
		OSZAR »

Iftikhar · ‎2019-05-02

I have script with below where clause but compiler is showing error. what could be the reason?

WHERE [WK_NUM_0] =Week(Today (1)));