Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
tyember1
Contributor III
Contributor III

Setting up Fiscal Week schedule

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.

1 Reply
tyember1
Contributor III
Contributor III
Author

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);