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.

[maxgro · Accepted solution]

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;

[pooja821]

i want it in qliksense.

[pooja821]

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

[maxgro]

the script  I posted works in QlikView and Qlik Sense

[d_prashanthredd]

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

[pooja821]

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.

[pooja821]

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

[pooja821]

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.

[maxgro]

Result with data from your excel, using the script below

Script

// load data from your excel

t:

LOAD Month, DOJ

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

// calculate the monthweek

u:

load *,

     currentweek - firstweek +1 as monthweek;

load

     Month,

     DOJ,

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

     week(DOJ) as currentweek

Resident t;

DROP Table t;

[d_prashanthredd]

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

Ok.. Will try