Group values are on the column chart by day of the week.
Transform your column chart into a week-based revenue visualization with this guide! Learn how to group data by day of the week (Monday to Sunday) using custom code in Sisense. Follow our step-by-step instructions to edit your widget, implement the script, and display total revenue for each day. Perfect for users looking to enhance their data analysis and insights. Note: Test the solution in your environment to ensure compatibility.
Enhance Your Sisense Dashboard with a Search Box for Filters
Navigating filters in Sisense dashboards can be cumbersome for users. To improve user experience, implementing a search box allows for quick access to specific filters, eliminating the need for extensive scrolling. This article provides a straightforward script to integrate a search feature into your dashboard, enhancing functionality and usability. The script includes creating a customizable search input and button, which filters visible items based on user input.Change the background color of the Pivot cell on value Windows
earn how to customize the background color of Pivot cells based on their values in Windows. This guide provides a simple JavaScript script that changes cell colors for categories like 'Male', 'Female', and 'Unspecified'. Enhance your Pivot widget by following easy steps to apply the script, ensuring your data visualization is not only informative but also visually appealing. Perfect for anyone looking to add a personal touch to their Pivot tables!Threshold-Based Conditional Plot Bands Colors For Cartesian and Polar Charts
Threshold-Based Conditional Plot Bands Colors For Cartesian and Polar Charts Plot bands in charts are visual elements that highlight specific ranges along the x-axis or y-axis, usually to emphasize certain areas of interest. These bands are typically shaded or colored differently from the rest of the chart, making them easy to distinguish. They often mark thresholds, target ranges, or critical areas. Here are some examples of where plot bands can be useful: In a performance chart, they can indicate acceptable and unacceptable performance ranges. In a control chart, they can highlight control limits and help identify deviation zones. In a financial chart, they can represent budget limits or profit margins, showing where financial performance is on target or off track. In a temperature chart, they can illustrate safe operating ranges for machinery or environmental conditions. In a sales chart, they can mark seasonal trends or sales targets to indicate where performance is expected to peak. The widget script below can be used to set conditional plot band (background) colors on Cartesian charts (Line, Column, Bar, Area, Scatter, and Box & Whisker) and Polar charts based on defined thresholds. /*** This script is used to create colored plot bands based on defined thresholds. This works for all Cartesian charts (Line, Column, Bar, Area, Scatter, Box & Whisker) as well as Polar chart. ***/ widget.on("processresult", function (widget, args){ args.result.yAxis[0].plotBands = [ //Define the color and the thresholds of each plot band in an object literal like shown in the examples below { color: '#ACACAC', from: Number.MIN_SAFE_INTEGER, //this represents the smallest integer value that can be safely represented in JavaScript -9007199254740991 to: -3 }, { color: '#25AD84', from: -3, to: -2 }, { color: '#FFB74B', from: -2, to: -1 }, { color: '#F5435C', from: -1, to: 0 }, { color: '#F5435C', from: 0, to: 1 }, { color: '#FFB74B', from: 1, to: 2 }, { color: '#25AD84', from: 2, to: 3 }, { color: '#ACACAC', from: 3, to: Number.MAX_SAFE_INTEGER //this represents the largest integer value that can be safely represented in JavaScript, i.e. 9007199254740991 } ]; }); For more information about customizing Sisense dashboards and widgets using JavaScript, please refer to these articles: Customizing Sisense using JavaScript Extend The Functionality Of An Existing Widget Highcharts API Documentation (for Highcharts-powered widget types: Column, Bar, Area, Pie, Polar, Scatter, and Box & Whisker)Blox with drop-down and All values in it
Add an "All Values" option to your BLOX dropdown menu with this simple guide! Perfect for when you first open your dashboard and want to filter all items. Follow our step-by-step instructions to edit your widget, implement the provided script, and easily customize your target dimension. Enhance your user experience with this quick solution. Note: Test the code in your environment to ensure compatibility.A Guide to Creating Bullet Charts
Instructions: Create a Bar Chart Add your main Category (ex. 'Company') Add your three required measures: Actual Value: This is the primary metric you are measuring. It's the "what happened" value. (ex. YTD Revenue, Actual Spend, Units Sold) Target Value: This is the goal or benchmark you are comparing against. It's the "what should have happened" value. (ex. Sales Quota, Budget, Last Year's Revenue) Qualitative Value (Forecast): This is a secondary comparative point, often used to show a projection. It's the "what we think will happen" value. (ex. Forecasted Spend, Projected Sales, Pipeline Value) Navigate to the widget's script editor: Paste the script into the widget's script editor and follow instructions: */ widget.on('processresult', function(widget, args) { // ======================================================== // CONFIGURATION // ======================================================== // --- Step 1: Field Names --- // These names MUST EXACTLY MATCH the names in your widget's Values panel. const actualValue_FieldName = 'Actual Value'; const targetValue_FieldName = 'Target'; const qualitative_FieldName = 'Qualitative'; // --- Step 2: Chart Labels --- // These are the clean names that will show up in the legend and tooltips. const actualValue_DisplayName = 'Actual Value'; const targetValue_DisplayName = 'Target'; const qualitative_DisplayName = 'Qualitative'; // Used for the marker line // --- Step 3: Color Palette --- // Define the colors for your chart elements. const color_ActualValue = '#3682ff'; // Blue for the main 'Actual Value' bar. const color_TargetValue_Fill = '#d3d3d3'; // Light gray for the background 'Target' bar. const color_TargetValue_Border = '#a9a9a9'; // Darker gray for the border. // --- EDIT MARKER COLORS HERE --- // Control the colors for the forecast marker based on your goal. // By default, OVER target is red and UNDER is green (good for tracking costs). // To make OVER target green (good for tracking revenue), just swap the color hex codes below. const color_Marker_Over = '#e45c5c'; // Color for when Forecast > Target const color_Marker_Under = '#4dbd33'; // Color for when Forecast < Target Add this directly underneath the script you just customized: if (!args.result || !args.result.series || args.result.series.length === 0) { return; } const result = args.result; const categories = result.categories; // Helper function to read data from your widget. const getDataBySeriesName = (name) => { for (let i = 0; i < result.series.length; i++) { if (result.series[i].name === name) { return result.series[i].data.map(point => typeof point === 'object' ? point.y : point); } } console.warn('Field not found in widget data:', name); return new Array(categories.length).fill(0); }; // --- DATA RETRIEVAL --- const actualValueData = getDataBySeriesName(actualValue_FieldName); const targetValueData = getDataBySeriesName(targetValue_FieldName); const qualitativeData = getDataBySeriesName(qualitative_FieldName); // Clear the default chart series to build our custom one. result.series = []; // --- PLOT OPTIONS --- result.plotOptions = { bar: { stacking: null, grouping: false, pointPadding: 0, groupPadding: 0.25, // Space between each bullet chart. borderWidth: 1, borderRadius: 3, // Rounded corners for the bars. dataLabels: { enabled: false } } }; // --- SERIES CONFIGURATION --- // 1. A hidden series to hold the raw qualitative data for the tooltip. result.series.push({ name: qualitative_FieldName, type: 'bar', data: qualitativeData.map((val, index) => ({ x: index, y: val })), visible: false, showInLegend: false }); // 2. Legend item for "Over Target". result.series.push({ name: 'Over Target', type: 'line', color: color_Marker_Over, marker: { symbol: 'line', lineWidth: 4, radius: 5 }, data: [], showInLegend: true }); // 3. Legend item for "Under Target". result.series.push({ name: 'Under Target', type: 'line', color: color_Marker_Under, marker: { symbol: 'line', lineWidth: 4, radius: 5 }, data: [], showInLegend: true }); // 4. The wide, background bar representing the Target value. result.series.push({ name: targetValue_DisplayName, type: 'bar', data: targetValueData.map((val, index) => ({ x: index, y: val })), color: color_TargetValue_Fill, borderColor: color_TargetValue_Border, pointWidth: 30, // Thickness of the background target bar. zIndex: 0, states: { hover: { enabled: false } }, showInLegend: true }); // 5. The narrower, foreground bar representing the Actual value. result.series.push({ name: actualValue_DisplayName, type: 'bar', data: actualValueData.map((val, index) => ({ x: index, y: val })), color: color_ActualValue, pointWidth: 15, // Thickness of the main actual value bar. zIndex: 1, showInLegend: true }); // 6. The thin vertical line that marks the Qualitative/Forecast value. result.series.push({ name: 'Qualitative Marker', type: 'bar', data: qualitativeData.map((val, i) => ({ y: val, color: (val - targetValueData[i]) >= 0 ? color_Marker_Over : color_Marker_Under })), pointWidth: 3, // Thickness of the marker line. zIndex: 2, showInLegend: false, enableMouseTracking: false }); // --- CHART & AXIS CONFIGURATION --- if (!result.chart) result.chart = {}; result.chart.inverted = true; // Flips chart to be horizontal. if (result.yAxis.title) result.yAxis.title.text = ''; // Hides Y-axis title. if (result.xAxis.title) result.xAxis.title.text = ''; // Hides X-axis title. // --- LEGEND & TOOLTIP --- result.legend = { ...result.legend, enabled: true, reversed: true }; result.tooltip = { enabled: true, shared: true, useHTML: true, backgroundColor: 'rgba(255, 255, 255, 1)', borderWidth: 1, borderColor: '#E0E0E0', formatter: function() { try { if (!this.points || this.points.length === 0) return false; const pointIndex = this.points[0].point.index; const categoryName = this.x; const chart = this.points[0].series.chart; const actualVal = chart.series.find(s => s.name === actualValue_DisplayName).data[pointIndex].y; const targetVal = chart.series.find(s => s.name === targetValue_DisplayName).data[pointIndex].y; const qualitativeVal = chart.series.find(s => s.name === qualitative_FieldName).data[pointIndex].y; const percentOfTarget = (targetVal === 0) ? 0 : (actualVal / targetVal); const varianceVal = qualitativeVal - targetVal; const varianceColor = varianceVal >= 0 ? color_Marker_Over : color_Marker_Under; let s = `<div style="padding: 10px; font-family: 'lato', sans-serif; font-size: 13px;">`; s += `<div style="font-size: 14px; margin-bottom: 10px; font-weight: 700;">${categoryName}</div>`; s += `<table style="width: 100%;">`; s += `<tr><td style="padding: 4px 2px;"><span style="background-color:${color_ActualValue}; width: 12px; height: 12px; border-radius: 2px; display: inline-block; margin-right: 8px;"></span>${actualValue_DisplayName}</td><td style="text-align: right; font-weight: 700;">${Highcharts.numberFormat(actualVal, 0, '.', ',')}</td></tr>`; s += `<tr><td style="padding: 4px 2px;"><span style="background-color:${color_TargetValue_Fill}; border: 1px solid ${color_TargetValue_Border}; width: 12px; height: 12px; border-radius: 2px; display: inline-block; margin-right: 8px; box-sizing: border-box;"></span>${targetValue_DisplayName}</td><td style="text-align: right; font-weight: 700;">${Highcharts.numberFormat(targetVal, 0, '.', ',')}</td></tr>`; s += `<tr><td style="padding: 4px 2px; padding-left: 24px;">% of Target</td><td style="text-align: right; font-weight: 700;">${Highcharts.numberFormat(percentOfTarget * 100, 0)}%</td></tr>`; s += `<tr><td style="padding: 4px 2px;"><span style="background-color:${varianceColor}; width: 3px; height: 12px; border-radius: 2px; display: inline-block; margin-right: 8px; margin-left: 4px;"></span>${qualitative_DisplayName}</td><td style="text-align: right; font-weight: 700;">${Highcharts.numberFormat(qualitativeVal, 0, '.', ',')}</td></tr>`; s += `<tr><td style="padding: 4px 2px; padding-left: 24px;">Variance</td><td style="text-align: right; font-weight: 700; color: ${varianceColor};">${Highcharts.numberFormat(varianceVal, 0, '.', ',')}</td></tr>`; s += `</table></div>`; return s; } catch (e) { return 'Error creating tooltip.'; } } }; }); Save script and refresh the widget. You're done!Pivot Table - Combine values with arrows
Hello Community, Recently I had the thought of combining a value with an arrow (or anything else I suppose!) within a pivot table. After getting inspiration from some links to the community https://community.sisense.com/t5/widget-dashboard-scripts/pivot-and-table-widget-scripting-for-replacing-values-with/ta-p/25842 https://community.sisense.com/t5/add-ons-plug-ins/pivot2-quot-add-image-indicator-to-a-pivot-table-quot-re/ta-p/9077 I mixed them up with some additional help and I ended up with a template script that: Based on the pre-defined targetColumns on a pivot table returns both the value & the arrow (example below) The script is: widget.on('initialized', function (ev, se) { // List of target columns by title var targetColumns = ['']; // add your column names here var colorGreen = 'green'; var colorRed = 'red'; var colorOrange = 'orange'; widget.transformPivot({ type: ['value', 'member'] }, function (metadata, cell) { if (metadata.measure && targetColumns.includes(metadata.measure.title) && metadata.rowIndex !== 0){ var originalValue = cell.value; var numericValue = (originalValue === null || originalValue === '') ? NaN : Number(originalValue); var arrowChar = '━'; // Default arrow var arrowColor = colorOrange; // Default color if (!isNaN(numericValue)) { if (numericValue > 0) { arrowChar = '▲'; arrowColor = colorGreen; } else if (numericValue < 0) { arrowChar = '▼'; arrowColor = colorRed; } else { arrowChar = '━'; arrowColor = colorOrange; } var percentage = (numericValue * 100).toFixed(1) + '%'; cell.content = percentage + ' ' + arrowChar; } else { cell.content = arrowChar; } cell.style.color = arrowColor; } } ); }); Notes: The arrows are included when exporting in PDF Hope you will find this helpful!!
Add presets to a Blox date filter widget
We have some dashboards that have widgets as filters. One of these is a Blox widget that functions as a date filter, which I created with help from the community here. I recently added presets to the date filter to make it easier and faster to apply date filtering. Create a Blox Widget Paste the script below in the script editor section. { "style": ".blox-slides button:hover{background-color:#014E66 !important;} .date-input-container { position: relative; } .date-input-container input[type='date'] { cursor: pointer; } .date-input-container::before { content: ''; position: absolute; top: 0; left: 0; right: 0; bottom: 0; z-index: 1; cursor: pointer; } .date-input-container input::-webkit-calendar-picker-indicator { opacity: 0; position: absolute; right: 10px; width: 20px; height: 20px; cursor: pointer; z-index: 2; }", "title": "", "showCarousel": true, "carouselAnimation": { "showButtons": false }, "script": "setTimeout(function() { const fromInput = document.getElementById('SelectVal_from'); const toInput = document.getElementById('SelectVal_to'); function formatDate(date) { const year = date.getFullYear(); const month = String(date.getMonth() + 1).padStart(2, '0'); const day = String(date.getDate()).padStart(2, '0'); return year + '-' + month + '-' + day; } function setDates(fromDate, toDate) { if (fromInput) fromInput.value = formatDate(fromDate); if (toInput) toInput.value = formatDate(toDate); } function getDateRanges() { const today = new Date(); const currentYear = today.getFullYear(); const currentMonth = today.getMonth(); const currentQuarter = Math.floor(currentMonth / 3); return { last30days: { from: new Date(today.getTime() - 30 * 24 * 60 * 60 * 1000), to: today }, quarter: { from: new Date(currentYear, currentQuarter * 3, 1), to: new Date(currentYear, (currentQuarter + 1) * 3, 0) }, ytd: { from: new Date(currentYear, 0, 1), to: today }, lastyear: { from: new Date(currentYear - 1, 0, 1), to: new Date(currentYear - 1, 11, 31) } }; } const ranges = getDateRanges(); document.querySelectorAll('[data-filter-type]').forEach(function(btn) { btn.addEventListener('click', function() { const filterType = this.getAttribute('data-filter-type'); if (ranges[filterType]) { setDates(ranges[filterType].from, ranges[filterType].to); } }); }); if (fromInput) { fromInput.addEventListener('click', function(e) { if (e.target.tagName === 'INPUT') { e.target.showPicker ? e.target.showPicker() : e.target.click(); } }); fromInput.style.cursor = 'pointer'; } if (toInput) { toInput.addEventListener('click', function(e) { if (e.target.tagName === 'INPUT') { e.target.showPicker ? e.target.showPicker() : e.target.click(); } }); toInput.style.cursor = 'pointer'; } }, 1000);", "body": [ { "type": "Container", "width": "90%", "style": { "margin": "0 auto" }, "items": [ { "type": "ActionSet", "actions": [ { "type": "date-preset", "title": "Last 30 Days", "style": { "color": "white", "background-color": "#007FAA" }, "data": { "FilterType": "last30days", "FilterFields": [ "[Dm_dates.date_data (Calendar)]" ] } }, { "type": "date-preset", "title": "This Quarter", "style": { "color": "white", "background-color": "#007FAA" }, "data": { "FilterType": "quarter", "FilterFields": [ "[Dm_dates.date_data (Calendar)]" ] } }, { "type": "date-preset", "title": "Year to Date", "style": { "color": "white", "background-color": "#007FAA" }, "data": { "FilterType": "ytd", "FilterFields": [ "[Dm_dates.date_data (Calendar)]" ] } }, { "type": "date-preset", "title": "Last Year", "style": { "color": "white", "background-color": "#007FAA" }, "data": { "FilterType": "lastyear", "FilterFields": [ "[Dm_dates.date_data (Calendar)]" ] } } ] }, { "type": "Container", "style": { "display": "flex", "flexDirection": "row", "justifyContent": "space-between", "marginTop": "20px", "gap": "10px" }, "items": [ { "type": "Container", "style": { "width": "48%" }, "items": [ { "type": "TextBlock", "text": "From", "weight": "lighter", "color": "black" }, { "type": "Container", "style": { "position": "relative" }, "items": [ { "type": "Input.Date", "id": "SelectVal_from", "placeholder": "mm/dd/yyyy", "calendar": true, "style": { "width": "100%", "padding": "14px", "background-color": "#F4F4F8", "border-radius": "8px", "border": "1px solid #ccc", "font-size": "16px", "cursor": "pointer" } } ] } ] }, { "type": "Container", "style": { "width": "48%" }, "items": [ { "type": "TextBlock", "text": "To", "weight": "lighter", "color": "black" }, { "type": "Container", "style": { "position": "relative" }, "items": [ { "type": "Input.Date", "id": "SelectVal_to", "placeholder": "mm/dd/yyyy", "calendar": true, "style": { "width": "100%", "padding": "14px", "background-color": "#F4F4F8", "border-radius": "8px", "border": "1px solid #ccc", "font-size": "16px", "cursor": "pointer" } } ] } ] } ] }, { "type": "ActionSet", "style": { "marginTop": "20px", "text-align": "center" }, "actions": [ { "type": "DateX", "id": "submit_btn", "title": "Apply", "style": { "color": "white", "background-color": "#007FAA" }, "data": { "FilterFields": [ "[Dm_dates.date_data (Calendar)]" ] } }, { "type": "filter-date-clear", "title": "Clear", "style": { "color": "white", "background-color": "#007FAA" }, "data": { "FilterFields": [ "[Dm_dates.date_data (Calendar)]" ] } } ] } ] } ] } Create the necessary actions for the buttons to work: date-preset const filterType = payload.data.FilterType; const filterDims = payload.data.FilterFields; const dash = payload.widget.dashboard; const now = new Date(); const yyyy = now.getFullYear(); const mm = String(now.getMonth() + 1).padStart(2, '0'); const dd = String(now.getDate()).padStart(2, '0'); const today = `${yyyy}-${mm}-${dd}`; let fromDate = ''; let toDate = today; //Year to date if (filterType === 'ytd') { fromDate = `${yyyy}-01-01`; //Quarter } else if (filterType === 'quarter') { const q = Math.floor(now.getMonth() / 3); const startMonth = q * 3 + 1; fromDate = `${yyyy}-${String(startMonth).padStart(2, '0')}-01`; //Last Year } else if (filterType === 'lastyear') { fromDate = `${yyyy - 1}-01-01`; toDate = `${yyyy - 1}-12-31`; // Last 30 days: from 30 days ago to today } else if (filterType === 'last30days') { const pastDate = new Date(now); pastDate.setDate(pastDate.getDate() - 30); const pastY = pastDate.getFullYear(); const pastM = String(pastDate.getMonth() + 1).padStart(2, '0'); const pastD = String(pastDate.getDate()).padStart(2, '0'); fromDate = `${pastY}-${pastM}-${pastD}`; } else { console.log('Unknown FilterType:', filterType); if (typeof sendResponse === 'function') sendResponse(false); return; } let newFilter = {}; $('#SelectVal_from').val(fromDate); $('#SelectVal_to').val(toDate); newFilter = { jaql: { dim: "", filter: { from: fromDate, to: toDate } } }; filterDims.forEach(function(dim) { newFilter.jaql.dim = dim; dash.filters.update(newFilter, { refresh: true, save: true }); }); Datex -- Apply button var today = new Date(); var dd = String(today.getDate()).padStart(2, '0'); var mm = String(today.getMonth() + 1).padStart(2, '0'); //January is 0! var yyyy = today.getFullYear(); today = yyyy + '-' + mm + '-' + dd; const filVal_from = payload.data.SelectVal_from == '' ? '1800-01-01' : payload.data.SelectVal_from; const filVal_to = payload.data.SelectVal_to == '' ? '2100-01-01' : payload.data.SelectVal_to; const filterDims = payload.data.FilterFields; const dash = payload.widget.dashboard; let newFilter = {}; console.log(filVal_from); console.log(filVal_to); newFilter = { jaql: { dim: "", filter: { from: filVal_from, to: filVal_to } } }; filterDims.forEach(function (dim) { newFilter.jaql.dim = dim; dash.filters.update(newFilter, { refresh: true, save: true }) }) Clear dates var today = new Date(); var dd = String(today.getDate()).padStart(2, '0'); var mm = String(today.getMonth() + 1).padStart(2, '0'); //January is 0! var yyyy = today.getFullYear(); today = yyyy + '-' + mm + '-' + dd; const filVal_from = "1800-01-01" const filVal_to = '2100-01-01' const filterDims = payload.data.FilterFields; const dash = payload.widget.dashboard; let newFilter = {}; newFilter = { jaql: { dim: "", filter: { from: filVal_from, to: filVal_to } } }; $('#SelectVal_from').val(''); $('#SelectVal_to').val(''); filterDims.forEach(function (dim) { newFilter.jaql.dim = dim; dash.filters.update(newFilter, { refresh: true, save: true }) }) Save everything and try it out.Highlight Widget's Info Button
One of the most underused (imo) dashboard functionalities is the widget's info button. Most dashboard designers want to include information/description for a specific Widget, expecting that the viewer will read it! Revising an old script, we enhanced it using some help and tested it on Linux versions, which effectively identifies all the dashboard's widgets that have a Description and highlights them based on the color defined in the script. Here is an example: The script is: // Handle 'widgetready' event to highlight widgets with descriptions dashboard.on('widgetready', function(_, ev) { const { widget } = ev; // Exit early if no widget or no valid description if (!widget || typeof widget.desc !== 'string' || !widget.desc.trim()) return; const highlightColor = 'red'; // Color to highlight the info icon const selector = `widget[widgetid="${widget.oid}"] widget-header .app-icon--general-info-circle`; $(selector).css('color', highlightColor); // Apply the color to the icon }); Hope you will find this helpful!!
