topic Re: getting week number from date in QlikView

getting week number from date

berryandcherry6 — Mon, 19 Dec 2016 06:20:22 GMT

Hi,

I am trying to get week number from dates, my week usually starts from mon - sun. Now i need to get week numbers from dates.

example: 19/12/2016 - 25/12/2016 as week 1

12/12/2016 - 18/12/2016 as week 2

05/12/2016 - 11/12/2016 as week 3 and so on till 52 week

Regards,

Supriya

Re: getting week number from date

MK_QSL — Mon, 19 Dec 2016 07:16:36 GMT

What is Week 1 in your case?

What is the logic behind creating this week numbers?

What are the possible week numbers based on your logic for next year (i.e. 2017)?

Re: getting week number from date

shraddha_g — Mon, 19 Dec 2016 07:20:01 GMT

Refer Custom Fiscal Calendar

Re: getting week number from date

shraddha_g — Mon, 19 Dec 2016 07:23:00 GMT

Also there is a script in Fiscal Year for week calculation for Fiscal in Comments.

Try below:

Set vFM = 12 ;    // First month of fiscal year
Set vFD = 0;     // First Day of the week (0=Mon, 1=Tue, ... , 6=Sun)

MasterCalendar:
Load distinct
   Dual(fYear-1 &'/'& fYear, fYear) as FYear,   // Dual fiscal year
   Dual(Month, fMonth)                as FMonth, // Dual fiscal month
   Dual('Q' & Ceil(fMonth/3), Ceil(fMonth/3)) as FQuarter,
   Ceil(([Request Date]-StartOfFWeekOne+1)/7) as FWeekNo,
   *;
Load Year + If(Month>=$(vFM), 1, 0)   as fYear,   // Numeric fiscal year
   Mod(Month-$(vFM), 12)+1            as fMonth, // Numeric fiscal month
   Dual('Q' & Ceil(Month/3), Ceil(Month/3)) as Quarter,
   WeekStart(FYearStart,0,$(vFD))     as StartOfFWeekOne,
   *;
Load
   [Request Date],
   YearStart([Request Date],0,$(vFM)) as FYearStart,
   Year([Request Date])               as Year,
   Month([Request Date])              as Month,
   Date(Monthstart([Request Date]), 'MMM-YYYY') as MonthYear,
   Week([Request Date])               as ISOWeekNo,
   Dual(WeekDay([Request Date]),Mod(WeekDay([Request Date]-$(vFD)),7)+1) as WeekDay,
   Day([Request Date])                as Day,
   Date([Request Date], 'MM/DD')      as DATEMMDD
Resident Work_Order;

Re: getting week number from date

berryandcherry6 — Mon, 19 Dec 2016 07:34:16 GMT

What is Week 1 in your case?

week 1 will be my current week from mon to sun, ie 19/12-25/12.

What is the logic behind creating this week numbers?

week numbers will be based on mon-sun in a week, whether it is this year ot next year. its only based on week of 7 days.

What are the possible week numbers based on your logic for next year (i.e. 2017)?

There wont be this question, its not year specific, only week specific, as i need current week to be week 1, and previous week to be week 2 whether its this year or next.

Re: getting week number from date

vinieme12 — Mon, 19 Dec 2016 07:56:42 GMT

have you read this?

Redefining the Week Numbers

Re: getting week number from date

MK_QSL — Mon, 19 Dec 2016 08:59:57 GMT

Let vMinDate = Num(YearStart(Today()));

Let vMaxDate = Num(YearEnd(Today()));

Load

Date(TempDate) as Date,

IF(Date('19/12/2016')+6 - TempDate < 0, 0, Div(Date('19/12/2016')+6 - TempDate,7)+1) as Week

;

Load

$(vMinDate) + IterNo() - 1 as TempDate

AutoGenerate 1

While $(vMinDate) + IterNo() - 1 <= $(vMaxDate);