Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
tstewart2
New Contributor

Fiscal Week

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?

3 Replies

Re: Fiscal Week

May be look here

Fiscal and Standard Calendar generation

and specifically for Weeks, look here

Redefining the Week Numbers

milindnikumbh
New Contributor III

Re: Fiscal Week

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
New Contributor III

Re: Fiscal Week

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;