Fiscal Week

[Anonymous]

Hi

I would like to create a Fical Week field in my calendar with the following conditions:

1). First week must start on the 26th of March

2). Week must be Saturday - Friday...see example below

Date                        Day                   FiscalWeek

26 March               Thur                         1

27 March                Fri                           1

28 March                Sat                          2

29 March                Sun                         2

30 March                Mon                        2

How can I dynamically create FiscalWeek field?

[sunny_talwar]

May be look here

Fiscal and Standard Calendar generation‌

and specifically for Weeks, look here

Redefining the Week Numbers

[milindnikumbh]

Hi Sunny ,

I tried HC logic but unfortunately not able to derive fiscal week number ( Fiscal year Apr to Mar ).

Can you please share the script ?

Thanks & Regards,

Milind.

[milindnikumbh]

Thanks all .  Resolved after minor changes in startdate and enddate .

My FMD table have all transactions with  date field. Column name is Date. By using FMD table , I have easily build the Fiscal Calendar . ( Fiscal year / Fiscal Quarter / Fiscal Month / Fiscal Week / Day... )

/* Fiscal year */

Temp:

Load min(Date) as mindate ,
max(Date) as maxdate
Resident FMD ;


Let vStartDate = Num(Peek('mindate' , 0, 'Temp'));
Let vEndDate = Num(Peek('maxdate' , 0, 'Temp'));

Drop table Temp;

SET vFiscalYearStartMonth = 4;

Set vCal_FD = 1; // First Day of the week {0=Mon, 1=Tue, ... , 6=Sun}

Set vCal_BW = 0; // Broken Weeks allowed {0=No, 1=Yes}

Set vCal_RD = 1; // Reference day = This day in Jan defines week one {1..7}

/*
LET vStartDate = NUM('04/03/2013');

LET vEndDate =Num('31/03/'& (Right(Today(),4)+1));
*/

FiscalCalendar:

LOAD

*

,Dual('Q' & Ceil(FiscalMonth/3), Ceil(FiscalMonth/3)) AS FiscalQuarter

,Dual(Month,FiscalMonth)

AS FiscalMonthName;

Load *,

Div( Date - WeekStart( WeekYearRefDate, 0, $(vCal_FD) ) + 7, 7 ) as WeekNumber,

Year( WeekYearRefDate ) as WeekYear;

Load *,

Date( YearStart( If( $(vCal_BW), Date, WeekRefDate )) + $(vCal_RD) - 1) as WeekYearRefDate;

Load *,

Date( WeekStart( Date, 0, $(vCal_FD) ) - $(vCal_RD) ) as WeekRefDate;

LOAD

*

,YeartoDate(Date)*-1 AS CurYTDFlag

,YeartoDate(Date,-1)*-1 AS LastYTDFlag

,inyear(Date, Monthstart($(vEndDate)),-1) AS RC12

,WeekDay(Date) AS WeekDay

,Day(Date) AS Day

,Year(Date)&'-'& week(Date) AS Year_Week

,Div(Date-YearStart(Date,0,4),7)+1 & '-' & WeekYear(Date) AS Week_Year

,Week(Date) AS Week // Standard Calendar Week

,date(monthstart(Date), 'MMM-YYYY') AS MonthYear

,Mod(Month(Date), 13) AS MonthNum // Standard Calendar Month Number

,Month(Date) AS Month // Standard Calendar Month

,Dual('Q' & Ceil(Month(Date)/3), Ceil(Month(Date)/3)) AS Quarter // Standard Calendar Quarter

,Year(Date) AS Year // Standard Calendar Year

,Div(Date-YearStart(Date,0,4),7)+1 AS FiscalWeek // Fiscal Week

,Mod(Month(Date) - $(vFiscalYearStartMonth), 12)+1 AS FiscalMonth // Fiscal Calendar Month

,YearName(Date, 0, $(vFiscalYearStartMonth)) AS FiscalYear

,AutoNumber(YearName(Date, 0, $(vFiscalYearStartMonth))) AS NumFiscalYear; // Fiscal Calendar Year

LOAD

Date($(vStartDate) + RangeSum(Peek('RowNum'), 1) - 1) AS Date

,RangeSum(Peek('RowNum'), 1) AS RowNum

AutoGenerate vEndDate - vStartDate + 1;