Setting up Fiscal Week schedule

tyember1 — Wed, 10 Jan 2018 15:02:24 GMT

Hello,

I have been trying to setup fiscal week in Qliksense according to my company's layout. For 2018, we have a 4,4,5 layout for each quarter (4 weeks, 4 weeks, 5 weeks). I am trying to represent that as a date dimension. I have seen the fiscal calendar post Fiscal Calendar with Non-Standard Days (Not 1-31), but I believe I am still missing a piece as the start date for each fiscal month is a different date. the first quarter for 2018 would go Dec 31 2017- Jan 27 2018, Jan 28 - Feb 24, Feb 25 - Mar 31.

We have created this in excel to represent the calendar, but I am finding it difficult to translate in this system to make it functional:

Function Create_Week_String(Yesterday As Date) As String

Create_Week_String = Yesterday - (Weekday(Yesterday) - 1) & "-" & Yesterday + (7 - Weekday(Yesterday))

End Function

Function Create_PTD_String(StartDate As Date, Yesterday As Date) As String

Dim IntWeek_Number As Integer

IntWeek_Number = CalcDate(StartDate, Yesterday)

Select Case IntWeek_Number

Case 1 To 4

Create_PTD_String = Format(StartDate, "mm/d/yyyy") & "-" & Format(StartDate + ((4 * 7) - 1), "mm/d/yyyy")

Case 5 To 8

Create_PTD_String = Format(StartDate + (7 * 4), "mm/d/yyyy") & "-" & Format(StartDate + ((8 * 7) - 1), "mm/d/yyyy")

Case 9 To 13

Create_PTD_String = Format(StartDate + (7 * 8), "mm/d/yyyy") & "-" & Format(StartDate + ((13 * 7) - 1), "mm/d/yyyy")

Case 14 To 17

Create_PTD_String = Format(StartDate + (7 * 13), "mm/d/yyyy") & "-" & Format(StartDate + ((17 * 7) - 1), "mm/d/yyyy")

Case 18 To 21

Create_PTD_String = Format(StartDate + (7 * 17), "mm/d/yyyy") & "-" & Format(StartDate + ((21 * 7) - 1), "mm/d/yyyy")

Case 22 To 26

Create_PTD_String = Format(StartDate + (7 * 21), "mm/d/yyyy") & "-" & Format(StartDate + ((26 * 7) - 1), "mm/d/yyyy")

Case 27 To 30

Create_PTD_String = Format(StartDate + (7 * 26), "mm/d/yyyy") & "-" & Format(StartDate + ((30 * 7) - 1), "mm/d/yyyy")

Case 31 To 34

Create_PTD_String = Format(StartDate + (7 * 30), "mm/d/yyyy") & "-" & Format(StartDate + ((34 * 7) - 1), "mm/d/yyyy")

Case 35 To 39

Create_PTD_String = Format(StartDate + (7 * 34), "mm/d/yyyy") & "-" & Format(StartDate + ((39 * 7) - 1), "mm/d/yyyy")

Case 40 To 43

Create_PTD_String = Format(StartDate + (7 * 39), "mm/d/yyyy") & "-" & Format(StartDate + ((43 * 7) - 1), "mm/d/yyyy")

Case 44 To 47

Create_PTD_String = Format(StartDate + (7 * 43), "mm/d/yyyy") & "-" & Format(StartDate + ((47 * 7) - 1), "mm/d/yyyy")

Case 48 To 52

Create_PTD_String = Format(StartDate + (7 * 47), "mm/d/yyyy") & "-" & Format(StartDate + ((52 * 7) - 1), "mm/d/yyyy")

End Select

End Function

Any help would be appreciated.

Thank you.

Re: Setting up Fiscal Week schedule

tyember1 — Wed, 10 Jan 2018 16:30:29 GMT

Update: The dates for the weeks are working out using the script found in Fiscal Calendar with Non-Standard Days (Not 1-31), but I cannot get December 31st into the week 1 of 2018 category. it is showing as week 53 of 2017 instead.

Below is my script:

LET vStartDate = MakeDate(2017, 1, 1);

LET vEndDate = MakeDate(2018, 12, 31);

LET vNumberOfDays = vEndDate - vStartDate + 1;

FiscalCalendar:

LOAD

Month(FiscalDate) AS Month,

Year(FiscalDate) AS Year,

'Q' & Ceil(Month(FiscalDate)/3) AS Quarter,

MonthName(FiscalDate) AS MonthYear,

'Q' & Ceil(Month(FiscalDate)/3) & '-' & Year(FiscalDate) AS QuarterYear;

LOAD Date,

Date(monthstart(Date-30,1)) as FiscalDate,

Day(Date(Date-30)) AS DayNumberInMonth;

LOAD

Date(makedate(2017)+recno()-1) as Date

AutoGenerate $(vNumberOfDays);

topic Re: Setting up Fiscal Week schedule in App Development

Setting up Fiscal Week schedule

Re: Setting up Fiscal Week schedule