Column chart with full range x-axis

I'd like to create a column chart for which the x-axis always has values from 0 to 52 in increments of 1. The purpose of the chart is to show how many weeks of inventory remains at each distribution center. The x-axis represents the number of weeks of inventory remaining, and the y-axis is the number of distribution centers.

I'd like to understand if a solution can be applied within the dashboard widget instead of the Elasticube. Since a Viewer can use the dashboard filters to select different item types instead of all items, generating 0 values in the Elasticube would cause a considerable amount of unnecessary data to be generated.

The attachment shows the current chart, which DOES NOT force all values from 0-52, and thus is visually misleading.

Regards,

Jim

James Haid

Posted 11 months ago·Last reply 10 months ago

7 comments

Tri Anthony

·11 months ago

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 Custom Code Transformation documentation.

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:

Result

James Haid

OP11 months ago

Hello TriAnthony ,

Thank you for sending over this Notebook. It looks like it will be the solution. Unfortunately, the Sisense cloud app won't let me upload it even though I changed it from .txt to .ipynb (I even tried .sipynb, and that didn't work). I made the Import Notebook attempts from a Mac desktop and Mac laptop, and tried both dragging the file over and looking it up via Browse (Browse did not recognize it as a valid file for upload). Thoughts on what be preventing this or if there's another attempt that will work? I'm okay if you email it to me if passing it thru the Community might be the issue.

Regards,

Jim

Tri Anthony

·11 months ago

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.

James Haid

OP10 months ago

Hello TriAnthony ,

I was able to upload the file you sent over, and it worked. Thank you for assisting in getting this resolved.

In the near future, I"ll look at making adjustments to support another scenario for which there are two/three values but no Break By field. The primary example is how we use numeric calendar week to compare this year's performance to last year's performance during the same calendar week (see attached). Any advance insight you can provide as to whether this scenario can/can't be supported with this approach?

Also, we're considering switching the numeric week along the x-axis for some widgets into the week ending date (ex. every Sunday, 2025-01-05...2025-09-14...2025-12-28) since it's easier for our customers to relate time of year to specific events/holidays that occurred. Can this approach work with dates as well?

Regards,

Jim

DRay

·11 months ago

Hello jameshaid ,

Thank you for reaching out. I haven't been able to find a solution for you, so I'm asking internally to try and get you an answer.

James Haid

OP11 months ago

Hello DRay - I saw this post earlier, and it doesn't appear to solve my question. From my perspective, the post appears to focus on manipulating existing x values, but not inserting new ones. Let me know if you feel one of the steps mentioned might be applicable.

Referencing my original screenshot, I'd like to see values 0 through 3 added at the start of the x-axis, and to have any skips filled in (ex. 15 to 18 should include 16 and 17). The challenge here may be that the x-axis is numeric since it represents # Of Weeks Of Inventory remaining.

Regards,

Jim

DRay

·11 months ago

Hi jameshaid,

Thank you for reaching out! This is an interesting question.

Can you take a look at this post and let us know if it helps? https://community.sisense.com/kb/widget_and_dashboard_scripts/modifying-x-axis-labels/9059