Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Folks,
I have dashboards with a Master Calendar. In them, I use the whole "WeekNum" calculation. But recently, I've been given a requirement that the Fiscal Year starts the "Week of August 1st". Now for a single year, it's easy to just add/subtract the difference from the WeekNum(SomeDate) value. But my calendar extends from 2010 -> Today().
How do I tell my calendar that when its calculating WeekNum, to use the week of August 1st as WeekNumb = 1? Right now, this is the pseudo-code I've got going in my head right now.
LOAD *,
If( CalDate > MakeDate(Year(CalDate),8,1),
Week(CalDate) - Week(MakeDate(Year(CalDate),8,1),
//Calc the difference in weeks below
Interval(Floor(CalDate - MakeDate(Year(CalDate) - 1,8,1)), 'W')
)
Any help is greatly appreciated.
The above is wrong, as you can end up with a Week 73. Below is the correct solution (for now)
Let varMinDate = Num(Makedate(2013,12,19));
Let varMaxDate = Num(Makedate(2020,1,3));
//Find all the '1st Weeks' that
//we will be calculating over
Let v2014 = Num(WeekStart((Makedate(2013,8,3))));
Let v2015 = Num(WeekStart((Makedate(2014,8,1))));
Let v2016 = Num(WeekStart((Makedate(2015,8,6))));
Let v2017 = Num(WeekStart((Makedate(2016,8,5))));
Let v2018 = Num(WeekStart((Makedate(2017,8,4))));
Let v2019 = Num(WeekStart((Makedate(2018,8,3))));
Let v2020 = Num(WeekStart((Makedate(2019,8,1))));
Let v2021 = Num(WeekStart((Makedate(2020,8,7))));
Let v2022 = Num(WeekStart((Makedate(2021,8,6))));
TempCalendar:
LOAD date($(varMinDate)+IterNo()-1) AS TempDate ,
Week($(varMinDate)+IterNo()-1) AS StandardWeek,
WeekStart($(varMinDate)+IterNo()-1) AS StandardWeekStart,
WeekEnd($(varMinDate)+IterNo()-1) AS StandardWeekEnd,
Year( WeekStart($(varMinDate)+IterNo()-1)) AS StandardYear
AUTOGENERATE (1)
WHILE $(varMinDate)+IterNo()-1<= $(varMaxDate)
;
NoConcatenate
FiscalCalendar:
LOAD DISTINCT StandardWeek,StandardWeekStart,StandardWeekEnd,StandardYear
Resident TempCalendar
Order by TempDate ASC;
DROP TABLE TempCalendar
;
FiscalCalendar2:
LOAD StandardWeekStart AS "Beginning Date",
WeekDay(StandardWeekStart) AS "Beginning Day",
StandardWeekEnd AS "Ending Date",
WeekDay(StandardWeekEnd) AS "Ending Day",
[PF Week Num] AS FiscalDateWeek,
[PF Year] AS Year
;
LOAD *, Floor((StandardWeekStart - StartingFencePost) / 7) + 1 AS [PF Week Num],
Year(StartingFencePost) + 1 AS [PF Year]
;
LOAD *, IF(StandardWeekStart < '$(v2014)', '$(v2013)',
IF(StandardWeekStart < '$(v2015)', '$(v2014)',
IF(StandardWeekStart < '$(v2016)', '$(v2015)',
IF(StandardWeekStart < '$(v2017)', '$(v2016)',
IF(StandardWeekStart < '$(v2018)', '$(v2017)',
IF(StandardWeekStart < '$(v2019)', '$(v2018)',
IF(StandardWeekStart < '$(v2020)', '$(v2019)',
IF(StandardWeekStart < '$(v2021)', '$(v2020)')
)
)
)
)
)
)
) AS StartingFencePost
Resident FiscalCalendar
ORDER BY StandardWeekStart
;
DROP TABLE FiscalCalendar
;
RENAME TABLE FiscalCalendar2 TO FiscalCalendar
;
STORE FiscalCalendar into [lib://Qlik Data (dts_qlikservice)/Test/PFWeeks.csv] (txt);
EXIT Script
;
Hi,
How about using
SET FirstMonthOfYear=8; - this will make start of the year from August.
SET FirstWeekDay=6;
SET BrokenWeeks=1; - you can use these two to set the week start day in that month.
Regards,
Sadasiva
Hello Sada,
Sorry it took me so long to get back to you. Unfortunately, that doesn't seem to work.
SET FirstMonthOfYear=8;
Weeklings:
LOAD * Inline [
FirstDate
'8/6/2017'
'8/7/2017'
'10/9/2017'
]
;
Weeklings2:
LOAD *, Week(FirstDate)
Resident Weeklings
;
DROP TABLE Weeklings
;
Exit Script
;
I would expect the dates 8/6/2017 to have a Week() value of 1 or two, but instead, the first week is still the week of Jan 1st.
This was my final answer. It's kind of sloppy as you can tell by the fact I have to throw away negative Fiscal Weeks which shouldn't have been created in the first place. But from what I can tell, it lines up.
The requirement for me was: The first week of the fiscal year ends on the first Sunday in August.
Let varMinDate = Num(Makedate(2015,7,1));
Let varMaxDate = Num(Makedate(2020,12,31));
Let v2014 = Num(WeekStart((Makedate(2013,8,3))));
Let v2015 = Num(WeekStart((Makedate(2014,8,1))));
Let v2016 = Num(WeekStart((Makedate(2015,8,6))));
Let v2017 = Num(WeekStart((Makedate(2016,8,5))));
Let v2018 = Num(WeekStart((Makedate(2017,8,4))));
Let v2019 = Num(WeekStart((Makedate(2018,8,3))));
Let v2020 = Num(WeekStart((Makedate(2019,8,1))));
Let v2021 = Num(WeekStart((Makedate(2020,8,7))));
Let v2022 = Num(WeekStart((Makedate(2021,8,6))));
TempCalendar:
LOAD date($(varMinDate)+IterNo()-1) AS TempDate
AUTOGENERATE (1)
WHILE $(varMinDate)+IterNo()-1<= $(varMaxDate)
;
FiscalCalendar:
LOAD DISTINCT *
WHERE FiscalDateWeek > 0
;
LOAD
WeekStart(FiscalDate) AS [Beginning Date],
WeekDay(WeekStart(FiscalDate)) AS [Beginning Day],
WeekEnd(FiscalDate) AS [Ending Date],
WeekDay(WeekEnd(FiscalDate)) AS [Ending Day],
If( Week(FiscalDate) = Week(TempStartingDate) AND Year(FiscalDate) = Year(TempStartingDate), 1
,
If( Year(FiscalDate) = Year(TempStartingDate), Week(FiscalDate) - Week(TempStartingDate) + 1
,
If( Week(YearEnd(TempStartingDate)) = 1,
Week(YearEnd(TempStartingDate) - 7) - Week(TempStartingDate) + Week(FiscalDate),
Week(YearEnd(TempStartingDate)) - Week(TempStartingDate) + Week(FiscalDate)
)
)
) AS FiscalDateWeek,
Year(TempEndingDate) AS Year
;
LOAD *,
Date(If(FiscalDate >= $(v2014) AND FiscalDate < $(v2015), $(v2014),
If(FiscalDate >= $(v2015) AND FiscalDate < $(v2016), $(v2015),
If(FiscalDate >= $(v2016) AND FiscalDate < $(v2017), $(v2016) ,
If(FiscalDate >= $(v2017) AND FiscalDate < $(v2018), $(v2017),
If(FiscalDate >= $(v2018) AND FiscalDate < $(v2019), $(v2018),
If(FiscalDate >= $(v2019) AND FiscalDate < $(v2020), $(v2019),
If(FiscalDate >= $(v2020) AND FiscalDate < $(v2021), $(v2020),
If(FiscalDate >= $(v2021) AND FiscalDate < $(v2022), $(v2021))
)
)
)
)
)
)
), 'MM/DD/YYYY') AS TempStartingDate,
Date(If(FiscalDate >= $(v2014) AND FiscalDate < $(v2015), $(v2015),
If(FiscalDate >= $(v2015) AND FiscalDate < $(v2016), $(v2016),
If(FiscalDate >= $(v2016) AND FiscalDate < $(v2017), $(v2017) ,
If(FiscalDate >= $(v2017) AND FiscalDate < $(v2018), $(v2018),
If(FiscalDate >= $(v2018) AND FiscalDate < $(v2019), $(v2019),
If(FiscalDate >= $(v2019) AND FiscalDate < $(v2020), $(v2020),
If(FiscalDate >= $(v2020) AND FiscalDate < $(v2021), $(v2021),
If(FiscalDate >= $(v2021) AND FiscalDate < $(v2022), $(v2022))
)
)
)
)
)
)
), 'MM/DD/YYYY') AS TempEndingDate
;
LOAD
TempDate AS FiscalDate,
Year (TempDate) AS FiscalYear,
Month (TempDate) AS FiscalMonth,
Day (TempDate) AS FiscalDay,
Week(TempDate) AS FiscalWeek
Resident TempCalendar
Order by TempDate ASC;
STORE FiscalCalendar into [lib://Qlik Data (dts_qlikservice)/Test/PFWeeks.csv] (txt);
EXIT Script
;
The above is wrong, as you can end up with a Week 73. Below is the correct solution (for now)
Let varMinDate = Num(Makedate(2013,12,19));
Let varMaxDate = Num(Makedate(2020,1,3));
//Find all the '1st Weeks' that
//we will be calculating over
Let v2014 = Num(WeekStart((Makedate(2013,8,3))));
Let v2015 = Num(WeekStart((Makedate(2014,8,1))));
Let v2016 = Num(WeekStart((Makedate(2015,8,6))));
Let v2017 = Num(WeekStart((Makedate(2016,8,5))));
Let v2018 = Num(WeekStart((Makedate(2017,8,4))));
Let v2019 = Num(WeekStart((Makedate(2018,8,3))));
Let v2020 = Num(WeekStart((Makedate(2019,8,1))));
Let v2021 = Num(WeekStart((Makedate(2020,8,7))));
Let v2022 = Num(WeekStart((Makedate(2021,8,6))));
TempCalendar:
LOAD date($(varMinDate)+IterNo()-1) AS TempDate ,
Week($(varMinDate)+IterNo()-1) AS StandardWeek,
WeekStart($(varMinDate)+IterNo()-1) AS StandardWeekStart,
WeekEnd($(varMinDate)+IterNo()-1) AS StandardWeekEnd,
Year( WeekStart($(varMinDate)+IterNo()-1)) AS StandardYear
AUTOGENERATE (1)
WHILE $(varMinDate)+IterNo()-1<= $(varMaxDate)
;
NoConcatenate
FiscalCalendar:
LOAD DISTINCT StandardWeek,StandardWeekStart,StandardWeekEnd,StandardYear
Resident TempCalendar
Order by TempDate ASC;
DROP TABLE TempCalendar
;
FiscalCalendar2:
LOAD StandardWeekStart AS "Beginning Date",
WeekDay(StandardWeekStart) AS "Beginning Day",
StandardWeekEnd AS "Ending Date",
WeekDay(StandardWeekEnd) AS "Ending Day",
[PF Week Num] AS FiscalDateWeek,
[PF Year] AS Year
;
LOAD *, Floor((StandardWeekStart - StartingFencePost) / 7) + 1 AS [PF Week Num],
Year(StartingFencePost) + 1 AS [PF Year]
;
LOAD *, IF(StandardWeekStart < '$(v2014)', '$(v2013)',
IF(StandardWeekStart < '$(v2015)', '$(v2014)',
IF(StandardWeekStart < '$(v2016)', '$(v2015)',
IF(StandardWeekStart < '$(v2017)', '$(v2016)',
IF(StandardWeekStart < '$(v2018)', '$(v2017)',
IF(StandardWeekStart < '$(v2019)', '$(v2018)',
IF(StandardWeekStart < '$(v2020)', '$(v2019)',
IF(StandardWeekStart < '$(v2021)', '$(v2020)')
)
)
)
)
)
)
) AS StartingFencePost
Resident FiscalCalendar
ORDER BY StandardWeekStart
;
DROP TABLE FiscalCalendar
;
RENAME TABLE FiscalCalendar2 TO FiscalCalendar
;
STORE FiscalCalendar into [lib://Qlik Data (dts_qlikservice)/Test/PFWeeks.csv] (txt);
EXIT Script
;