Week Number from Date

[pooja821]

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.

[d_prashanthredd]

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.

[pooja821]

Thanks Massimo.

[cmorri1988]

LOAD

     Date,     

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

FROM...

[pooja821]

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.

[cmorri1988]

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

[pooja821]

this is my script

load *,

     currentweek - firstweek +1 as Hiring week;

load

      DOJ,

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

     week(DOJ) as currentweek ,*;

[maxgro]

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;

[pooja821]

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.

[Iftikhar]

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

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