Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
i want it in qliksense.
how would we do that. i.e subtracting number from date.
the script I posted works in QlikView and Qlik Sense
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
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.
I want month wise week number instead of Year wise Week Number.
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.
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;
You mean, a week may contain 1 day as if the date 2nd is of Monday.
Ok.. Will try