Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)?
Refer Custom Fiscal Calendar
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;
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.
have you read this?
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);