Fiscal Calendar with Weekly Reference Table – Handling Overlapping Months
Hi Qlik Community,
I'm currently building a fiscal calendar in Qlik Sense based on a weekly reference table (attached as FiscalWeekCalender.xlsx) that includes:
FISCALYEARKEY
FISCAL Week No.
Start date
End date
My goal is to generate a MasterCalendar that looks like the one I've also attached (ExpectedFiscalMasterCalender.xlsx), where each date is linked to its respective fiscal week, fiscal month, and fiscal year.
❗Challenge
I'm having issues with fiscal week numbers and fiscal month assignment, especially in weeks that span across two calendar months. For example:
📅 2024 Week 05: 29-Apr-24 to 05-May-24 This week overlaps both April and May, and I’m not sure how best to handle the fiscal month in this case — should it be split, or assigned fully to the starting month?
Weeks are always overlapping months. It's not related to the normal calendars or fiscal ones. The views of weeks and months aren't compatible to each other and therefore it should be avoided to mix them. Each kind of adjustments will have more or less side-effects and leading usually only to confusion.
The only way to avoid an overlapping would be to use a 4-4-5 or similar calendar but they aren't compatible against the normal/fiscal ones and assuming that the fiscal ones is your master-view it won't lead further.
