1 Reply Latest reply: Jan 10, 2018 11:30 AM by Tyler Emberson RSS

    Setting up Fiscal Week schedule

    Tyler Emberson

      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
          Tyler Emberson

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