Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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);