Creating 'From-To' Date using BloX
Analytical Need Attached below is a BloX template. A common request is to have a 'From To' input at the top of the dashboard to affect a calendar range selection of dates. To achieve this need we can utilize BloX's form capabilities. Picking a date range in a calendar of a 'Date' filter and looking into the jaql created by it shows the following: We can use input fields to change the date range values selected in the from:to keys. Solution We will use the skeleton that creates an action that affects the selected filters: Make sure to change the "filterName" to the right filter column name. Also, make sure to use the right jaql that needs to be populated. Example: { "type": "ActionSet", "actions": [ { "type": "Filters", "title": "Submit", "data": { "filters": [ { "filterName": "Days in Date", "filterJaql": { "from": "", "to": "", "custom": true } } ] } } ] } Then, make sure to create the required Input field to pass on the input value and populate the from/to: { "type": "Input.Date", "id": "data.filters[0].filterJaql.from", "placeholder": "yyyy-mm-dd", "defaultValue": "", "style": { "width": "100%" }, "borderRadius": "4px", "borderStyle": "none", "backgroundColor": "#F4F4F8" } The "id" should hold the entire path of the key that needs to be populated with the value. If you prefer you can use this JSON template file for the full script: json script { "card": { "style": "", "script": "", "title": "", "showCarousel": true, "body": [ { "type": "Container", "width": "90%", "style": { "margin": "0 auto" }, "items": [ { "spacing": "large", "type": "TextBlock", "text": "From Date", "weight": "light", "color": "black" }, { "type": "Input.Date", "id": "data.filters[0].filterJaql.from", "placeholder": "yyyy-mm-dd", "defaultValue": "", "style": { "width": "100%" }, "borderRadius": "4px", "borderStyle": "none", "backgroundColor": "#F4F4F8" }, { "spacing": "medium", "type": "TextBlock", "text": "To Date", "color": "black" }, { "type": "Input.Date", "id": "data.filters[0].filterJaql.to", "placeholder": "yyyy-mm-dd", "defaultValue": "", "style": { "width": "100%" }, "borderRadius": "4px", "borderStyle": "none", "backgroundColor": "#F4F4F8" }, { "type": "ActionSet", "actions": [ { "type": "Filters", "title": "Submit", "data": { "filters": [ { "filterName": "Days in Date", "filterJaql": { "from": "", "to": "", "custom": true } } ] } } ] } ] } ] }, "config": { "fontFamily": "Open Sans", "fontSizes": { "default": 16, "small": 14, "medium": 22, "large": 32, "extraLarge": 50 }, "fontWeights": { "default": 400, "light": 100, "bold": 800 }, "containerStyles": { "default": { "backgroundColor": "#ffffff", "foregroundColors": { "default": { "normal": "#3A4356" }, "white": { "normal": "#ffffff" }, "grey": { "normal": "#9EA2AB" }, "orange": { "normal": "#f2B900" }, "yellow": { "normal": "#ffcb05" }, "black": { "normal": "#000000" }, "lightGreen": { "normal": "#93c0c0" }, "green": { "normal": "#2BCC7F" }, "red": { "normal": "#FA5656" }, "accent": { "normal": "#2E89FC" }, "good": { "normal": "#54a254" }, "warning": { "normal": "#e69500" }, "attention": { "normal": "#cc3300" } } } }, "imageSizes": { "default": 40, "small": 40, "medium": 80, "large": 120 }, "imageSet": { "imageSize": "medium", "maxImageHeight": 100 }, "actions": { "color": "", "backgroundColor": "", "maxActions": 5, "spacing": "large", "buttonSpacing": 20, "actionsOrientation": "horizontal", "actionAlignment": "right", "margin": "0", "showCard": { "actionMode": "inline", "inlineTopMargin": 16, "style": "default" } }, "spacing": { "default": 5, "small": 5, "medium": 10, "large": 20, "extraLarge": 40, "padding": 0 }, "separator": { "lineThickness": 1, "lineColor": "#D8D8D8" }, "factSet": { "title": { "size": "default", "color": "default", "weight": "bold", "warp": true }, "value": { "size": "default", "color": "default", "weight": "default", "warp": true }, "spacing": 20 }, "supportsInteractivity": true, "imageBaseUrl": "", "height": 197 } }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.Advanced Dynamic Filtering
Get quick, comparative insights from large datasets by creating flexible pivot tables or widgets. Whether comparing time periods, countries, or sales figures, you can leverage a single filter to handle multiple comparisons without clutter. This method uses custom fields, measured values, ranking filters, and the smart label plugin to streamline comparisons across various dimensions for clean, dynamic visualizations.Having difficulty with Dashboard performance
Struggling with slow-loading dashboards? Optimize performance by reducing widget count to 6-8 per dashboard, utilizing plugins like Jump to Dashboard and Switchable Dimensions, and minimizing data load through filters and pre-aggregation. Avoid heavy pivot tables and complex graph functions, and streamline data models by joining on integers and applying data security in a single location. For detailed guidance, check out our performance troubleshooting and optimization resources.How to Calculate Absolute Sum
How to Calculate Absolute Sum We have a table with data and we are going to calculate the absolute SUM for [sum] column. We divide this into two parts. First of all, we SUM all positive values with the formula ([Total sum],[sum1]) The first argument is a SUM and second is a filter With the help of it, we select all positive values. The second part of the formula will be ABS(([Total sum],[sum1])) The first argument is the SUM of the values, the second filter of all negative values And all this calculation wraps with the function ABS (you can read more here). The whole formula will have a look: ([Total sum],[sum])+ABS(([Total sum],[sum1])) And the final result of it will be 5 Check out this related content: Academy course Sisense DocumentationCompare A Time Window Against The Immediately Preceding Time Window Of The Same Size.
Users could sometimes want to compare performance on a particular time period vs the immediately preceding period. Sometimes those periods are not of a common size like months or quarters. It could be a campaign that lasts a few days, or an arbitrary date range based on topics trending on social media. In those cases, you would want to select any time range and be able to compare to a previous period of the same arbitrary size, and be able to change that time range and see the comparison adjust dynamically. Challenges In Sisense, the RANGE function receives literal numbers, but it doesn’t accept numeric expressions (like Count([Days in Date]). Sisense has last, next and offset operators for filters, but no out of the box ways to adjust them according to the number of members in a filter. Solution: By using the Javascript API and in formula filters, we can make a formula go back the number of days in the filter, and also apply an offset of the same number of days, to get the same data for the preceding time window. Example: We want to be able to get the number of orders in an N day date range, and the growth vs the previous N days. For example, if the filter is 2/11/2020 to 2/20/2020, we want the growth vs the 2/1/2020 to 2/10/2020 period. We'll use an indicator widget for this, with the next formulas: Value: Count([OrderID) Secondary: (Count([OrderID]) - (Count([OrderID]),[Days in Date]))/(Count([OrderID]),[Days in Date]) And set the formula filters to one of the predefined “Last N” options: To learn about filtering in formulas, check the documentation: Document Here Finally, open the Edit Script editor for the widget, and enter the following script. The script will search for formula filters that use the table and column specified at the beginning of the script (and use the last operator), and replace the count and offset with the number of members in the filter. If the calendar was used to set a days filter, it will calculate the difference in days to come up with the number of members: If you change the filter to a different date range, the filters in the growth formula will adjust dynamically to the new time window. Script: var TABLE = "Date"; var COLUMN = "Date"; widget.on("beforequery", function(scope, widget) { // getting item var item = widget.query.metadata.find(function(i) { return i.panel === "scope" && $$get(i, "jaql.table") === TABLE && $$get(i, "jaql.column") === COLUMN }); if (!defined(item)) { console.log("DynamicLastX: Can't find a filter item with '" + TABLE + "." + COLUMN +"' dimension. Aborting."); return; } var calFilter = false; var calCount = 0; var dateTo = null; // getting member var members = $$get(item, "jaql.filter.members"); if (!defined(members)) { console.log("DynamicLastX: No item filter. Assuming calendar was used."); calFilter=true; var dateFrom = new Date(item.jaql.filter.from + "T00:00:00"); var dateTo = new Date(item.jaql.filter.to + "T00:00:00"); var diffTime = dateTo.getTime() - dateFrom.getTime(); calCount = diffTime / (1000 * 3600 * 24) + 1; } var formulaFilters = []; var filterMember = 0 var filterCount = 0 if (!calFilter) { filterMember = members[0]; filterCount = members.length } else { filterMember = item.jaql.filter.to + "T00:00:00"; filterCount = calCount; } widget.query.metadata.forEach(function(item) { if ((item.source === 'value' || item.source === 'secondary') && item.jaql && item.jaql.context) { for (var lmnt in item.jaql.context) { if (item.jaql.context[lmnt].table === TABLE && item.jaql.context[lmnt].column === COLUMN && item.jaql.context[lmnt].filter && item.jaql.context[lmnt].filter.last) { formulaFilters.push(item.jaql.context[lmnt].filter); } } } }) if (formulaFilters && filterMember) { formulaFilters.forEach(formulaFilter => { formulaFilter.last = { offset: filterCount, count: filterCount, anchor: filterMember }; }); } });Change multiple dashboards owner
In case when you need to change the owner of multiple dashboards there are few ways: 1) In case all the dashboards are in 1 folder then you can select the folder and export the dashboards. After what, import the dashboards back under the user to whom you would like to transfer the dashboards. In this case you will need to republish the dashboards to users to whom they were shared before. 2) Change the user email in the user profile under Admin tab-> Users - ONLY to a new user. This option will not merge the accounts! 3)Use the script below under Sys.Admin in Web Developers console (F12 in Chrome) Note, Dashboards and Elasicubes must be shared in advance! var newOwnersId = "5e5cdc80ecebb629e0c8de85" //New owner, use the REST API to get user ID $.ajax({ url: '/api/v1/dashboards?fields=oid,owner', success: function(res) { console.log(res) var dashboards = res.filter(function(dash) { return prism.user._id === dash.owner }) var dashboardsList = []; dashboards.forEach(function(dashboard) { dashboardsList.push(dashboard.oid) }) if (dashboardsList.length) { changeOwnership(dashboardsList) } } }) function changeOwnership(dashboardsList) { var succeed = []; var failed = [] dashboardsList.forEach(function(dash) { $.ajax({ method: 'POST', contentType: 'application/json', data: JSON.stringify( { "ownerId": newOwnersId, "originalOwnerRule": "edit" } ), url: '/api/v1/dashboards/' + dash + '/change_owner?adminAccess=true', async: false, success: function(res) { succeed.push(dash) }, error: function(res) { failed.push(dash) } }) }); console.log('Dashboard\'s owner was changed successfully for: '); console.log(succeed); console.log('Dashboard\'s owner wasn\t changed successfully. Please, try to change owner manually for: '); console.log(failed); }Sharing a file from a local machine in a dashboard
Sisense has the capability to share a local file (excel, word, pdf etc.) with dashboard users. Note: The file must be saved on the server in the following path: %programfiles%\Sisense\PrismWeb. You can create a new folder or save it under 'Resources'. Solution: Add a text widget and select to add a hyperlink. Under 'Target URL' in the following format: http\\:<IP adress:Port>/folder_path/file (The ip can be obtained from the 'ipconfig' command or copying the link under dashboard republish) Example: The URL for the file 'widget.csv' that in located at %programfiles%\Sisense\PrismWeb\Documents is: http://192.168.5.124:8081/documents/widget.csv.Reorder pivot columns by viewers
Need A common requirement for self service BI is to enable the viewers to save their own report. This can be achievable in Sisense if you are a designer of a dashboard. However, when you are a viewer, you cannot even re-order the pivot columns as you please. Challenge The default behaviour of the viewer is only to interact with the filters and view the numbers. No layout changes are allowed for a viewer. Solution We can change the default behaviour of the viewer and allow him to re-order the rows, values & columns. Using the API of custom roles we can achieve that. Solution: Go to Admin ==> REST API ==> 0.9 PUT /roles/{idOrName}/manifest/{path} API Fill in the following parameters: idOrName: consumer path: /widgets.items manifest: { "reorder": true } Execute the API and that's it! Note: the re-order option is enabled for all widget types (not just pivots), however, pivots are the main use case for this need.Maximize Dashboard Performance
What is good dashboard performance? Dashboard performance is the time it takes to load a Sisense dashboard. We want our users to have a good experience when working with our data. The time taken to load the dashboard is the first step. Each individual widget in a dashboard is a separate query. The dashboard needs to send these queries through the Microsoft IIS web server to the ElastiCube, pull back the results and populate the data visualizations. The questions we will address in this post are what is a reasonable dashboard load time and how can we enhance performance. So, what is good dashboard performance? This is a slightly subjective question. Users always want the fastest load time. However, we must think about load time relative to the data we are processing. If the dashboard is aggregating billions of rows of data, it is unrealistic to expect two-second load times. The converse is true as well — if a dashboard is based on a few thousand rows of data, the dashboard should not take five minutes to load. The goal, to put it simply, is to optimize the load time relative to the amount of data the dashboard is processing. Sisense’s architecture is designed to return queries very quickly, and there are ways to help it. How to monitor performance Timing First and foremost, how long does the dashboard take to load after the user navigates to the URL? Does the load time inhibit the user experience? As discussed before, the answer will vary by the use case, ElastiCube size, and end user. However, as a general guideline we want the load time to be under a minute if the dashboard is not crunching enormous amounts of data. System Resources Monitoring system resources during dashboard load is the first step to identifying possible issues. These can be monitored live by opening the Task Manager > Performance tab. RAM and CPU should not be maxed out during the dashboard load. If they are, it could be hindering the process. More system resources might be necessary (see here for Sisense's minimum hardware guidelines). Components ElastiCube.exe – if this process is taking CPU and RAM then the query is too heavy. This could be a possible many to many occurrence (covered below) ElastiCubeManagement Service – if this process is taking CPU and RAM, you most likely have a build occurring at the same time Java(TM) Platform SE Binary - This process transmits Sisense monitoring data. Heavy use of hardware is expected when restarting the service after a while (as it catches up on transmitting data), but if you experience continued consumption, confirm you are using the latest version of the Sisense Monitor Node.js - If this process is heavily consuming resources, check whether you have recently published dashboards to a large number of users. w3wp / iisexpress – if this process is taking CPU and RAM you may be overloading your IIS server. This is mostly likely when presenting too much data (covered below) Another way of monitoring system resources is by using the Windows Performance monitor. This will help analyze system resources over time. The post here should help: https://learn.microsoft.com/en-us/windows-server/identity/ad-fs/deployment/configure-performance-monitoring Timing vs other dashboards Using other dashboards that use similar data sets as a baseline can also be useful. For example, two dashboards are using the same ElastiCube. Dashboard-A takes 30 seconds to load. Dashboard-B takes five minutes. Knowing that Dashboard-A loads normally, we can tell that Dashboard B has some kind of an issue. Possible issues could be a bad join, many-to-many relationship, or specific troublesome widget that Dashboard A did not have. Compare the differences to narrow down possible issues. Web Developer Console Developer Tools within your web browser allow us to track each dashboard component's load time. In Chrome before loading the dashboard, open the developer console (F12 in Chrome). Navigate to the Network tab. From there you can see the timeline and the long-running requests. Most likely it will be the JAQL queries that take the longest. You can then click those records for more information. Under Headers>Request Payload there is information about the widget ID and Name. This will help identify the long-running queries. Under Timing, you can view what composed the query response time. Stalled represents one query waiting for other queries to finish (there is a limit to the number of concurrent queries your web browser can run). Waiting (TTFB) represents time waiting for the ElastiCube to respond. Content Download represents the amount of time retrieving the query results. If the JAQL queries are not the longest running components, you may have connectivity issues to your server, or are using too much Javascript (covered below). Causes of slowness System Resources A reasonable query time depends on a few factors. First and foremost, system resources. The recommended system resources for Sisense can be found here: https://docs.sisense.com/win/SisenseWin/windows-minimum-requirements.htm. Amount of data The amount of data crunched/returned in a query has an impact on performance. Logically, this makes sense. Crunching one million rows will, of course, return faster than one billion rows. The way to affect this is by adjusting filters to limit the data returned. Furthermore, limiting the amount of data in the ElastiCube is useful. This can be done by editing either the tables that Sisense is connected to, or by editing the SQL statement that is used to query the original data source. Schema Design Schema design is a very important factor in query performance. Each data connection within the ElastiCube will become an inner join when the dashboard queries this data. Joins are costly when it comes to query time. When designing the schema for performance, the ElastiCube designer should de-normalize the schema as much as possible. De-normalization means having a fewer amount of joins, and including redundant information in one table. For more on schema de-normalization, see here. Custom Calculations in the Dashboard Custom calculations are often useful when manipulating data to answer business questions. They allow users to aggregate data in various ways to present visually. However, they do take time to process. Each custom calculation is a separate query that is processed by Sisense. To enhance query performance we can cut down the number of queries generated by the dashboard. This is done by moving some calculations to the ElastiCube, thus moving that process time to the build time instead of dashboard load. The best example is a row calculation, such as the addition of two separate columns — FieldA + Field B. This type of calculation can be easily added to a table using the same exact formula as a custom field. If it is from a different table, we can use the LOOKUP function in the ElastiCube Manager. Presenting too much data Presenting large amounts of data is costly on dashboard load time as well. More specifically this causes slowness when using a pivot or table widget with many rows. The web server has to process the large amount of data and store it to allow the tool to present it visually. If it is possible, limit the number of dimensions that break the data to such a granular form. Sisense generally recommends 6-8 widgets per dashboard for optimal performance. Each widget incurs incremental query and rendering time. Sisense's official add-ons such as Accordion, Jump to Dashboard, and Switchable Dimensions may be leveraged to reduce the number of widgets while preserving the amount of data accessible to end users. When an end user loads the first dashboard in a session, performance will be slower against subsequent dashboard loads, as common web application elements such as logos, toolbars, and code templates are loaded for the first time. Later navigation between dashboards once these resources are cached will be faster. Other causes of query slowness and slow dashboard performance Many to Many If RAM grows exponentially, and the dashboard takes a very long time to load, you could have a possible many to many relationship. A many to many relationship (M2M) can cause extreme slowness in dashboard load time. A M2M is a relationship in the ElastiCube where neither side of the relationship is unique. This creates a Cartesian product, thus consuming all of the machine's resources. Relationship Cycle A relationship cycle exists when there are multiple join paths between two tables in a data model. Sisense will generally opt for the shortest path, but depending on the dimensions used (in widgets or filters), Sisense can take join paths through tables that are unexpected to dashboard designers. While not as deleterious to performance, an additional join on a large table can reduce performance and possibly lead to unexpected results. For more information on relationship cycles in data modeling, please visit the Handling Relationship Cycles documentation page. For the detection of data model issues such as many-to-many relationships and cycles, we recommend using the official Visualize Queries plugin. Data Security If some users are seeing slow performance but others are not, it is worthwhile to check the data security of the user in question. Some questions to consider: Are there complex queries occurring on the ElastiCube due to competing data security settings? Does the data brought in by data security cause pivot tables or other widgets to display a larger amount of data than expected? Please reference the official documentation on data security here. Browser Compatibility If some users are seeing slower dashboard performance on certain machines or browsers, check to make sure they are using a support browser outlined in the Minimum Requirements and Supported Platforms document.
