Week over week analysis with custom fiscal year: Use case of a fuel and convenience retail operator
Introduction Week-over-week (WoW) analysis is a key part of performance tracking for fast-moving, high-traffic businesses such as convenience stores, gas stations, and car washes. For these organizations, aligning the fiscal calendar with operational cycles rather than the standard calendar year makes reporting more meaningful. In this use case, the fiscal year begins on the closest Sunday to January 1st, ensuring each year starts with a full week. This structure simplifies weekly reporting and keeps week-to-week comparisons consistent across years, which is important for tracking trends like fuel sales, store traffic, and service volumes. While nonstandard, this setup is commonly used in practice. What the Solution Does For standard, fixed calendar or fiscal years, week-over-week analysis can be achieved using the “First Month of Fiscal Calendar” and “First Day of Week” settings, along with the PASTYEAR function. However, for dynamic fiscal years that begin on a weekday closest to January 1st, these features don’t provide a usable solution, since the start date can fall in the previous or following calendar year. The solution uses the https://www.sisense.com/marketplace/add-on/filtered-measure/ and a custom dashboard script to handle the custom fiscal year. Two year filters are added to the dashboard: one represents the selected fiscal year (user-selectable), and the other represents the prior year for comparison (locked and optionally hidden), which is automatically set with a dashboard script. The Filtered Measure plugin applies the selected-year filter to the measure for the chosen period, while the prior-year filter applies to the measure for the corresponding period in the previous year. This approach ensures that week-over-week calculations respect the custom fiscal calendar, providing accurate comparisons across equivalent weeks. Note: In this particular implementation, the fiscal years and week numbers are pre-calculated in the database and stored as numeric columns. To create a Date dimension table in your Elasticube with fiscal years starting on the first Sunday closest to January 1st, refer to the SQL example below. Why It’s Useful This solution addresses the native functional limitation by respecting the custom fiscal calendar, ensuring weekly trends are comparable across years. As a result, teams can reliably track key metrics, such as fuel sales, store traffic, and service volumes, on a true week-by-week basis, supporting better operational planning and more informed decision-making. Attachments WeekoverWeekAnalysiswithCustomFiscalYear.dash.txt (dashboard) Sample ECommerce - Custom Fiscal Year.smodel.txt (elasticube) JS Script - Automatic Update for Second Year Filter.txt (dashboard script) SQL Query - Dim Date with Custom Fiscal Year.txt (custom table SQL query) For the script to hide the second filter, refer to this BINextLevel article: https://www.binextlevel.com/post/hide-dashboard-filters. Note: remove the .txt extension before importing the dashboard (.dash) and the Elasticube (.smodel) files.
Scripting Issue - second script prevents first script from working
A colleague is having a scripting issue where one appears to stop the first from working. The first one (const columnsToRenderAsHTML = [1];) renders the field as HTML while the second one changes the cell color depending on the text. Each works fine on its own, but when the two are together only the second one functions. Excuse my ignorance if this has a simple answer, but I'd genuinely appreciate the Community's assistance here.
