TriAnthony
Sisense Employee
Joined 4 years ago
User Profile
User Widgets
Contributions
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.Re: Dashboard Design Guard - A plugin for enforcing dashboard best practices and organizational standards
Hi HamzaJ, Just wanted to let you know that I've updated the article and uploaded an updated version of the plugin (the link in both the article and the comment above has also been updated), which includes a few improvements and bug fixes. -TriRe: Dashboard Design Guard - A plugin for enforcing dashboard best practices and organizational standards
Hi HamzaJ, I've updated the plugin to allow a third mode "warn", which will show a pop-up warning instead of disabling/removing the UI elements. I'll update the article soon, but I thought I’d send you a copy of the plugin in advance (attached below). There are a few more configurations that I added, all of them are documented in the config.js file. Please let me know if you have any feedback. Thank you! Example of the pop-up warning:Re: Column chart with full range x-axis
Hi jameshaid, I just sent the file to your registered email. If you're still unable to import the notebook, you can create a new one instead. In the "Create a new Notebook" window, give it a name and add two additional input parameters (located at the bottom of the window). All the other fields are optional. Here are the Additional Input Parameters configurations: WeekNumberFieldName parameter: BreakByFieldName parameter: Next, open the code editor (Jupyter Notebook) and delete all existing cells. Paste the below code into the first empty cell: # Test Cell # When the notebook is executed by the widget, this cell is ignored. # See the `Test Cell` section below for further details. from init_sisense import sisense_conn df_input = sisense_conn.get_debug_data() additional_parameters = '{\"WeekNumberFieldName\":\"Week Number\", \"BreakByFieldName\":\"Age Range\"}' Then paste the below into the next cell: import json import pandas as pd # Load the additional_parameters JSON string try: data = json.loads(additional_parameters) except json.JSONDecodeError as e: print(f"Failed to parse outer JSON: {e}") raise week_number_field_name = data["WeekNumberFieldName"] break_by_field_name = data["BreakByFieldName"] week_numbers = range(1, 53) # Get unique values for break_by_field_name break_by_field = df_input[break_by_field_name].unique() # Build full cartesian product of week_number_field_name and break_by_field_name full_index = pd.MultiIndex.from_product( [week_numbers, break_by_field], names=[week_number_field_name, break_by_field_name] ) # Rebuild the data frame df_result = ( df_input.set_index([week_number_field_name, break_by_field_name]) .reindex(full_index, fill_value=0) .reset_index() ) df_result Save the code and switch back to the widget tab. Click Next, enter the names of your Week Number and Break By columns in the input fields. Click Done to close the window, then save the widget.Re: Column chart with full range x-axis
Hi jameshaid, You can use Python code to add the missing records via Custom Code Transformation. I have attached a notebook below that contains the Python code for adding missing weeks (based on week numbers) to charts. Note that the community site doesn’t currently support .ipynb files. Please change the file extension from .txt to .ipynb after downloading it. For instructions on how to import and use a Custom Code notebook, please refer to the https://docs.sisense.com/main/SisenseLinux/transforming-query-results-with-python.htm#AddingNotebooks. The notebook requires two inputs: Week Number Field Name and Break By Field Name. Use the names of the corresponding columns as they appear in the widget editor: the week number column for Week Number Field Name, and the Break By column (in your example, the column that contains KEHE, RAINFOREST DISTRIBUTION, and UNFI) for Break By Field Name. Please see the screenshots below for reference. Let me know if you have any follow-up questions. Custom Code configurations: ResultPlugin - Enable Multicolor Legend Items for Conditional and Range
Overview Legend items come with colored boxes that represent the data points in the chart. The color of each box matches the bar/column/bubble in the chart, as long as the measure is set to a single color, and not conditional or range. When conditional or range is used, the legend item's box becomes grey (or the palette's default color for Scatter Charts), because the box cannot natively accommodate multiple colors. This plugin solves this limitation by: splitting the box into smaller strips to accommodate multiple colors, for conditional, or creating a gradient of two colors in the box, for range. Limitations This plugin is only relevant to Bar Charts, Column Charts, and Scatter Charts. Other chart types either do not support conditional/range or have their own version of legends (e.g., Area Map). If there are too many legend items, wider boxes might affect spacing and cause certain items to be cut off. See the Configuration section below for more details. Installation Instructions Download and unzip the plugin zip file. Extract the files directly (do not extract into a new folder); the plugin files are already organized within a folder in the zip file. Upload the multiColorLegendItems folder to the plugins directory on your Sisense server (/opt/sisense/storage/plugins). Check the Add-ons page in the Admin tab (under Server & Hardware) and make sure the new plugin is listed and enabled. Modify the config file as needed (this can be done before of after the upload). Configurations This plugin comes with a configuration file that can be updated as needed. The four configuration settings in the file are: numberOfColorsForWiderBoxes . If there are too many conditions, the box may be too small to accommodate all colors, so the width may need to be increased. In this case, specify the threshold for the number of colors. For example, a threshold of 5 means the box width will be increased if there are five or more colors assigned to it. Otherwise, the original width will be kept. Note: Wider boxes might affect spacing and cause certain legend items to be cut off if there are too many of them. To work around this, the verticalLayout setting can be set to true. The default value is 5. widthMultiplierForConditional . For wider boxes, specify the multiplier. For example, a multiplier of 1.5 means the new width will be 1.5 times the original width for boxes that have more colors than the numberOfColorsForWiderBoxes threshold. The default value is 1.5. widthMultiplierForGradient . If wider boxes are needed for range as well, specify the multiplier. Otherwise, leave the default value of 1. The original width is generally sufficient for range. verticalLayout . Specify whether legend items should be laid out vertically. This is useful when wider boxes are needed and there are too many legend items to fit correctly horizontally. The default value is false. Examples 1. Conditional with 4 colors (less than the numberOfColorsForWiderBoxes threshold) [ALT Text: A comparison of two column charts labeled "Before" and "After," displaying sales and costs data by sales associates. The "Before" chart shows the "Sales" legend item box in grey despite conditional formatting being applied, while the "After" chart correctly displays the four conditional colors.] 2. Conditional with 11 colors (greater than the numberOfColorsForWiderBoxes threshold, the legend item box width is increased) [ALT Text: A comparison of two scatter charts labeled "Before" and "After," displaying sales and costs data by sales associates. The "Before" chart shows the "Sales" legend item box in blue (the palette's default color) despite conditional formatting being applied, while the "After" chart correctly displays all eleven conditional colors, with a wider box to fit them.] 3. Range Auto (both the min and max colors are auto-selected) [ALT Text: A comparison of bar scatter charts labeled "Before" and "After," displaying sales and costs data by sales associates. The "Before" chart shows the "Sales" legend item box in grey despite automatic range (gradient) being applied, while the "After" chart correctly displays a gradient between the automatically selected minimum and maximum colors.] 4. Range Manual with Min Max range type (both the min and max colors are manually selected) [ALT Text: A comparison of bar scatter charts labeled "Before" and "After," displaying sales and costs data by sales associates. The "Before" chart shows the "Sales" legend item box in grey despite manual range (gradient) with Min/Max mode being applied, while the "After" chart correctly displays a gradient between the user-selected minimum and maximum colors.] 5. Range Manual with Min range type (only the min color is manually selected) [ALT Text: A comparison of bar scatter charts labeled "Before" and "After," displaying sales and costs data by sales associates. The "Before" chart shows the "Sales" legend item box in grey despite manual range (gradient) with Min mode being applied, while the "After" chart correctly displays a gradient between the user-selected minimum color and the automatically selected maximum color.] 6. Range Manual with Max range type (only the max color is manually selected) [ALT Text: A comparison of bar scatter charts labeled "Before" and "After," displaying sales and costs data by sales associates. The "Before" chart shows the "Sales" legend item box in grey despite manual range (gradient) with Max mode being applied, while the "After" chart correctly displays a gradient between the automatically selected minimum color and the user-selected maximum color.]
