Last 4 digits formula
I am an ESO user and am trying to create a widget that will show only the final 4 digits of an incident number. The beginning of the incident numbers are not always the same but the last 4 are what I need. In excel I use =right(A2,4). I then use this list in excel with an =if formula (=if(c3-c2=1,"","missing") to show me any incident numbers that were not reported. If there is a way to make this part of the widget also, it would make my job that much easier. Thank you.
Widget Error
I’m working on integrating a few Sisense dashboards into my project, but I’m running into this error. import React, { useMemo, useEffect, useState } from 'react'; import { useApi, configApiRef } from '@backstage/core-plugin-api'; import { SisenseContextProvider, WidgetById } from '@sisense/sdk-ui'; import { filterFactory } from '@sisense/sdk-data'; import SisenseDashboard from './SisenseDashboard'; import solutionSisenseOrg from '../../solution-sisense-org-mapping.json'; const SISENSE_URL = ''; const SISENSE_TOKEN = ''; type SisenseOrgMapping = Record<string, Record<string, any>>; type SisenseChartProps = { dashboardOid: string; tab: string; widgetLists?: []; isWidgetRequired?: boolean; }; const centerStyle = { minHeight: '40vh', display: 'flex', flexDirection: 'column', alignItems: 'center', justifyContent: 'center', fontSize: '1.1rem', background: 'none', }; export const SisenseChart: React.FC<SisenseChartProps> = ({ dashboardOid, tab, widgetLists = [], isWidgetRequired = true, }) => { const config = useApi(configApiRef); const { sisenseUrl, sisenseToken } = useMemo( () => ({ sisenseUrl: config.getOptionalString('sisense.url') ?? SISENSE_URL, sisenseToken: config.getOptionalString('sisense.token') ?? SISENSE_TOKEN, }), [config], ); // Extract current org from URL const currentUrl = window.location.href; const solutionOrg = useMemo(() => { const match = currentUrl.match(/\/system\/([^/]+)/); return match?.[1] ?? ''; }, [currentUrl]); // Map to Sisense org const mappedSisenseOrg = useMemo(() => { if (!solutionSisenseOrg || typeof solutionSisenseOrg !== 'object') return undefined; return (solutionSisenseOrg as SisenseOrgMapping)[solutionOrg]; }, [solutionOrg]); // State for dashboard filters const [filters, setFilters] = useState<any[]>([]); const [isLoading, setLoading] = useState(true); const [error, setError] = useState<string | null>(null); // Reset filters/loading when dashboardOid or tab changes useEffect(() => { setFilters([]); setLoading(true); setError(null); }, [dashboardOid, tab]); // Fetch dashboard filters useEffect(() => { if (!dashboardOid || !sisenseUrl || !sisenseToken) return; const fetchDashboard = async () => { try { const response = await fetch( `${sisenseUrl}/api/v1/dashboards/${dashboardOid}`, { headers: { Authorization: `Bearer ${sisenseToken}` } }, ); if (!response.ok) throw new Error('Failed to fetch dashboard'); const data = await response.json(); setFilters(data.filters || []); setLoading(false); } catch (err: any) { setError('Failed to load dashboard filters.'); setLoading(false); } }; fetchDashboard(); }, [dashboardOid, sisenseUrl, sisenseToken]); console.log('Fetched filters:', filters); // Memoize jaqlFilters const jaqlFilters = useMemo(() => { // Extract filters from levels const levelFilters = filters.flatMap( (filter: any) => filter.levels?.filter( (level: any) => level.table === 'projects' || level.table === 'products' || // (level.table === 'Business_Unit_Sols' && level.column === 'solution') || (level.table === 'rca_all_issues' && (level.column === 'solutions' || level.column === 'incident_date' || level.column === 'incident_date (Calendar)')) || (level.table === 'defect_metrics' && level.column === 'project_name') || (level.table === 'quality_scorecard' && level.column === 'filter_solution'), ) || [], ) || []; // Extract root jaql filters const rootJaqlFilters = filters .filter((filter: any) => filter.jaql) .map((filter: any) => filter.jaql) .filter( (jaql: any) => (jaql.table === 'rca_all_issues' && (jaql.column === 'solutions' || jaql.column === 'incident_date' || jaql.column === 'incident_date (Calendar)')) || (jaql.table === 'Business_Unit_Sols' && jaql.column === 'solution') || (jaql.table === 'projects') || (jaql.table === 'products') ); return [...levelFilters, ...rootJaqlFilters]; }, [filters]); // Memoize jaql, attribute, filterquery (always called, never conditionally) let hasValidFilter = null; let members = null; if (mappedSisenseOrg && mappedSisenseOrg[tab]) { if (typeof mappedSisenseOrg[tab] === 'string') { members = [mappedSisenseOrg[tab]]; hasValidFilter = Boolean( jaqlFilters.length && jaqlFilters[0]?.dim && mappedSisenseOrg && mappedSisenseOrg[tab], ); } else if (typeof mappedSisenseOrg[tab] === 'object') { hasValidFilter = Boolean( jaqlFilters.length && jaqlFilters[0]?.dim && mappedSisenseOrg && mappedSisenseOrg[tab] && mappedSisenseOrg[tab][dashboardOid], ); members = [mappedSisenseOrg[tab][dashboardOid]]; } } const jaql = useMemo( () => hasValidFilter ? { dim: jaqlFilters[0]?.dim, datatype: jaqlFilters[0]?.datatype || 'text', isDashboardFilter: jaqlFilters[0]?.isDashboardFilter || false, title: jaqlFilters[0]?.title, collapsed: jaqlFilters[0]?.collapsed || false, filter: { explicit: true, multiSelection: true, members: members ? members : [], }, } : null, [members, hasValidFilter, jaqlFilters], ); const attribute = useMemo( () => hasValidFilter ? { jaql: () => ({ jaql: { dim: jaqlFilters[0]?.dim, datatype: jaqlFilters[0]?.datatype || 'text', isDashboardFilter: jaqlFilters[0]?.isDashboardFilter || false, title: jaqlFilters[0]?.title, collapsed: jaqlFilters[0]?.collapsed || false, filter: { explicit: true, multiSelection: true, members: members ? members : [], }, }, }), serialize: () => ({ dim: jaqlFilters[0]?.dim, datatype: jaqlFilters[0]?.datatype || 'text', title: jaqlFilters[0]?.title, }), id: jaqlFilters[0]?.dim, } : null, [members, hasValidFilter, jaqlFilters], ); const filterquery: any = useMemo(() => { if (!hasValidFilter || !attribute || !jaql) return null; return filterFactory.customFilter(attribute as any, jaql as any, { disabled: false, }); }, [hasValidFilter, attribute, jaql]); if (!sisenseUrl || !sisenseToken) return null; if (!solutionOrg) return ( <div style={centerStyle as React.CSSProperties}> Could not determine organization from URL. </div> ); if (!mappedSisenseOrg) return ( <div style={centerStyle as React.CSSProperties}> No Sisense organization mapping found for "{solutionOrg}". </div> ); if (error) return <div style={centerStyle as React.CSSProperties}>{error}</div>; if (isLoading) return ( <div style={centerStyle as React.CSSProperties}> <div className="sisense-loader" style={{ marginBottom: 12 }}> {' '} Loading...{' '} </div> </div> ); if (!hasValidFilter || !filterquery) { return ( <div style={centerStyle as React.CSSProperties}> No valid filter found for this {tab} dashboard. </div> ); } const renderWidgets = () => ( <div> {widgetLists.map((widgetOid, idx) => ( <WidgetById key={widgetOid || idx} // Ensure key is never null widgetOid={widgetOid} dashboardOid={dashboardOid} includeDashboardFilters={false} filters={[filterquery]} /> ))} </div> ); let contentToShow: React.ReactNode = null; if (isWidgetRequired && widgetLists.length > 0) { contentToShow = renderWidgets(); } else if (!isWidgetRequired && widgetLists.length === 0) { contentToShow = null; } else { contentToShow = ( <SisenseDashboard filterquery={filterquery} dashboardOid={dashboardOid} /> ); } return ( <SisenseContextProvider url={sisenseUrl} token={sisenseToken}> {contentToShow} </SisenseContextProvider> ); }; --------------------- import React from 'react'; import { Dashboard, dashboardModelTranslator, useGetDashboardModel, useCustomWidgets, } from '@sisense/sdk-ui'; import CustomHistogramWidget from './CustomHistogramWidget'; interface SisenseDashboardProps { filterquery: any[]; dashboardOid: string; } const SisenseDashboard: React.FC<SisenseDashboardProps> = ({ filterquery, dashboardOid, }) => { const { dashboard } = useGetDashboardModel({ dashboardOid, includeFilters: false, includeWidgets: true, }); const { registerCustomWidget } = useCustomWidgets(); if (!dashboard) return null; const { title, widgets, layoutOptions, styleOptions, widgetsOptions } = dashboardModelTranslator.toDashboardProps(dashboard); registerCustomWidget('histogramwidget', CustomHistogramWidget); // console.log('Dashboard Props:widgets', widgets); // Add a non-null key property to each widget using its id console.log('Dashboard Widgets:', layoutOptions); const widgetsWithValidId = Array.isArray(widgets) ? widgets.map((widget: any, idx: number) => { let id = typeof widget?.id === 'string' && widget.id.trim() ? widget.id.trim() : `widget-${idx}`; if (!id) { console.warn('Widget missing id:', widget); id = `widget-${idx}`; } const widgetType = widget.widgetType || widget.type || widget.kind || widget.category || ''; const widgetProps: any = { ...widget, id, key: id}; if ( widgetType !== 'text' && widgetType !== 'TextWidget' && widgetType !== 'TEXT' ) { widgetProps.filters = filterquery; } else { delete widgetProps.filters; } return widgetProps; }) : []; return ( <div style={{ marginBottom: '24px' }}> <Dashboard id={dashboardOid} title={title} layoutOptions={layoutOptions} widgets={widgetsWithValidId} filters={filterquery} config={{ filtersPanel: { visible: false } }} styleOptions={styleOptions} widgetsOptions={widgetsOptions} /> </div> ); }; export default SisenseDashboard; ERROR: 1. dimension, [rca_all_issues.incident_date (Calendar)], was not found. rcas_and_related_actions {"dimension":"[rca_all_issues.incident_date (Calendar)]"} 2. Value cannot be null. (Parameter 'key') rcas_and_related_action
Percentage in formulas
Hello! I'm trying to work out how to get a percentage value in a formaula. The results are not what I'm expecting so i imagine it's my maths letting me down.. The end result needs to be a percentage count of all safeguarding events ever. What my formula is currently looking like is: ([# of unique EventUID], [Was the incident a safeguarding?]) / ([# of unique EventUID]) * 100 With [Was the incident a safeguarding?] being filtered to TRUE. This gives me a result of 266% Base values for objects are: [# of unique EventUID] = 19,761 [Was the incident a safeguarding?] N/A = 18,321 FALSE = 876 TRUE = 564 Any help would be appreciated
How to pivot my data and get counts based on the min and max date value
Morning Sisense community, I need to pivot my data and get counts based on the min and max date value. Given the below data: row date count Example 1/01/2025 35 Example 2 1/01/2025 20 Example 1/02/2025 35 Example 2 1/02/2025 10 Example 1/04/2025 34 Example 2 1/04/2025 10 Example 1/08/2025 34 Example 2 1/08/2025 10 I want to pivot it to: Row Start Count End Count Example 35 34 Example 2 20 10 I need to be able to filter my data in the dashboard. So, if the date is filter to “1/04/2025” to “1/08/2025” in the dashboard, the pivot needs to update to: Row Start Count End Count Example 34 34 Example 2 10 10 How would I achieve this?How to filter a table/pivot table: Filter one column that does not equal another column?
Hi DRay Liliia_DevX , I am trying to see if there is logic where I have a pivot table and I want to filter one column based on another column that do not equal, so getting all the results that match within a widget filter or maybe a formula filter.CASE WHEN logic in Formulas
Hi DRay , I am trying to use the CASE WHEN logic in the formulas but does not seem to work. My logic: SUM(IF([Total DEFINITIVE_EST_NUM_OF_ER_VISITS]>0, [Total DEFINITIVE_EST_NUM_OF_ER_VISITS], 0)) but this does not give a value of 0 when I am looking in a tooltip map, it stills shows N/A in the tooltip. I tried a CASE WHEN Statement: CASE ( WHEN [Total DEFINITIVE_EST_NUM_OF_ER_VISITS]> 0 THEN [Total DEFINITIVE_EST_NUM_OF_ER_VISITS] ELSE 0 END) But I get unexpected token, and from the documentation I am doing everything correctly. The goal is for the tooltip information for Hospitals with ER visits that have 0 visits to show up as 0 rather than N/A in the tooltip information.
