topic Re: Fiscal week starting from Monday. in QlikView

Fiscal week starting from Monday.

hamzabob1 — Thu, 16 May 2019 11:49:10 GMT

I am trying to start my week in fiscal calendar from Monday.It starts from Monday only for the current year but for the previous year it starts from Sunday.I want to start the week from Monday for all the years.

Below is the expression i've used:

Div(Date-YearStart(Date,0,4),7)+1 as Week,

Kindly help,

Regards,

Hamza

Re: Fiscal week starting from Monday.

johnca — Thu, 16 May 2019 15:28:10 GMT

Why not set the first day of the week to Monday in the script where all the initial environment variables are set...

SET FirstWeekDay=7;

The default is 6 and equates to Sunday. 7 equates to Monday, etc...

HTH,

John

Re: Fiscal week starting from Monday.

hamzabob1 — Fri, 17 May 2019 03:56:38 GMT

Hii John,

Thanks for your rpl,but still am facing the same issue..

hamza

Re: Fiscal week starting from Monday.

Brett_Bleess — Wed, 26 Jun 2019 12:19:31 GMT

The following is in regard to fiscal year, but it may be of some help in getting you on the right track, it is a design blog post from Henric Cronstrom, hopefully it gets you a bit further on things. There are a bunch of other posts out there as well, so I would be sure to search further if this one does not get you exactly what you need, as there may be another with the additional detail you need...

https://community.qlik.com/t5/Qlik-Design-Blog/Fiscal-Year/ba-p/1472103

Regards,
Brett

Re: Fiscal week starting from Monday.

johnca — Wed, 26 Jun 2019 14:37:06 GMT

Thanks for that link Brett. I ended up playing with that far too long, but was able to create something close. It needs some finagling to get it to work YoY but I did get it to set the start week to 1 on April 1st. Getting it to not count week 1 UNTIL Monday in the first week of the year is the next challenge. Otherwise it works okay.

/* Set date range for calendar */

LET vDateMin = Num(MakeDate(2015,4,1));
LET vDateMax = Floor(MonthEnd(Today()));
LET vDateToday = Num(Today());

TempCalendar:
LOAD
Date($(vDateMin) + RowNo() - 1) AS TempDate
AUTOGENERATE 1 WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

/* HC's code, modified for vFW and fWeek below; */

Set vFM = 4 ; // First month of fiscal year
Set vFW = 14 ; /* Added to get the First week of fiscal year, as close to April 1st as possible */

Calendar:
Load TempDate as Date, /* date to join to other data */
WeekDay, /* To verify day of week when week starts */
Year + If(Month>=$(vFM), 1, 0) as fYear, // Numeric fiscal year
Mod(Month-$(vFM), 12)+1 as fMonth, // Numeric fiscal month
Mod(Week-$(vFW), 52)+1 as fWeek /* Added for the week. Not perfect but can be modified to get what you need */
;
Load TempDate,
Year(TempDate) as Year, // Your standard master calendar
Month(TempDate) as Month,
Week(TempDate) as Week,
WeekDay(TempDate) as WeekDay
RESIDENT TempCalendar
ORDER BY TempDate ASC;

DROP TABLE TempCalendar;

Anyway, this should help Hamza...