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.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 }; }); } });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.How to create a Personalized Navigation Landing Page
Requirement In this article, we will look to create a personalized dashboard navigation landing page that shows: The logged user's most popular dashboards. Most popular dashboards on the company level. Consider further enhancing the logic presented in this article. Solution Steps: 1) To address the need, we will first need to learn how to implement a landing page - Follow this article to learn more on that. The reasoning behind it is we will use data-security where we will give a logged user permissions to see: His own records - Retrieving his popular dashboards The dummy user records - Retrieving the most popular dashboards on a company level. 4) Add data-security rules to the model that enables the user to see his own UserID/UserName from DimUsers table + UserId/UserName '-1'. 5) Find the attached BloX template that introduced action buttons to jump to a specific dashboard. LandingPage.json: { "card": { "style": "", "script": "", "title": "", "showCarousel": false, "body": [ { "spacing": "", "type": "Container", "items": [ { "type": "TextBlock", "text": "{panel:Dashboard Name}", "horizontalAlignment": "center", "size": "medium", "style": { "color": "#117899" } }, { "spacing": "small", "type": "TextBlock", "text": "{panel:Total Column} # of Actions", "color": "default", "horizontalAlignment": "center" } ] } ], "actions": [ { "type": "Action.OpenUrl", "title": "{panel:Dashboard Name}", "url": "http://10.50.57.72:30845/app/main#/dashboards/{panel:Dashboard ID}" } ] }, "config": { "fontFamily": "Open Sans", "fontSizes": { "default": 16, "small": 12, "medium": 22, "large": 32, "extraLarge": 50 }, "fontWeights": { "default": 500, "light": 100, "bold": 1000 }, "containerStyles": { "default": { "backgroundColor": "lightgrey", "foregroundColors": { "default": { "normal": "#3A4356" }, "white": { "normal": "#ffff" }, "grey": { "normal": "#5C6372" }, "orange": { "normal": "#f2B900" }, "yellow": { "normal": "#ffcb05" }, "black": { "normal": "#000000" }, "lightGreen": { "normal": "#3ADCCA" }, "green": { "normal": "#54a254" }, "red": { "normal": "#dd1111" }, "accent": { "normal": "#2E89FC" }, "good": { "normal": "#54a254" }, "warning": { "normal": "#e69500" }, "attention": { "normal": "#cc3300" } } } }, "imageSizes": { "default": 40, "small": 40, "medium": 80, "large": 160 }, "imageSet": { "imageSize": "medium", "maxImageHeight": 100 }, "actions": { "color": "", "backgroundColor": "#10a5F5", "maxActions": 5, "spacing": "small", "buttonSpacing": 20, "actionsOrientation": "horizontal", "actionAlignment": "center", "showCard": { "actionMode": "inline", "inlineTopMargin": 16, "style": "default" } }, "spacing": { "default": 5, "small": 20, "medium": 40, "large": 60, "extraLarge": 40, "padding": 25 }, "separator": { "lineThickness": 1, "lineColor": "#eeeeee" }, "factSet": { "title": { "size": "default", "color": "default", "weight": "bold", "warp": true }, "value": { "size": "default", "color": "default", "weight": "default", "warp": true }, "spacing": 20 }, "supportsInteractivity": true, "height": 842, "imageBaseUrl": "" } } 6) Import the attached dashboard example NavigationDashboard (1).dash. You can learn more on how to customize BloX in the following documentation page: https://www.sisense.com/marketplace/blox-widgets/ That's it!Showing The Aggregated Value Of The Selected Date And The Last Year’s Same Day Of The Same Week
Analytical Need It is always a common ask from the business to compare the daily metric of a selected date and then compare it to the last year same period metric. The same period could sometimes be the same day of the same week of last year – for example, if we are looking at the number of orders for 12/23/2019 (which is the Monday of Week 52 of year 2019), we want to compare it against the Last year’s same day of the same week (which is the Monday of Week 52 of 2018 – 12/24/2018). Modeling Challenge Usually when we want to slice data by time context, we use the date dimension table but that will not be enough to go back to the last year’s same week number and day number. In this case, we would need to map each date to its equivalent date for this kind of use case. With the normal date dimension table and the available date functions, we cannot do this. Solution If you look at the logic to do a date to date mapping on a date to the last year same day of the same week, we must go back by 52 weeks i.e., 364 days (52*7) from any given day to find the equivalent day in the previous year. STEP I Note: We are using the date dimension table from this post and we are naming it ‘Dim_Date_Updated’. Now, create a custom column named Last_Year_Same_Day using the following expression. createdate(getyear(Date)-1,getmonth(Date),getday(Date)) Ensure the custom column has a data type of Date Time and then, run a ‘Changes Build’ for the column to be built into the model. Step II Now that we have two Date fields in the ‘Dim_Date_Updated’ table, we can use them to do simultaneous analysis on the two dates at the same. But to facilitate this, we would need the fact table (transaction table) to be connected to both the date fields by duplicating the fact table. We need to first duplicate the Fact table (‘Fact_Orders’) and name it as ‘Fact_Orders_LY’, then connect Dim_Date_Updated table to Order_Date of Fact_Orders and Order_Date of Fact_Orders_LY using Date and Last_Year_Same_Day respectively. T The data model with the tables should look like below: Now, let us execute a Full Build. Building Widget: The goal is to build an indicator widget which shows the number of Orders for this year’s date as the Primary Value and that of the last year’s date as the secondary. We would have to use the Fact_Orders_LY only in this widget but the rest of the dashboard can use the Fact_Orders table. Let us first set up a dashboard filter on the ‘Date’ field of the ‘Dim_Date_Updated’ table and set the context to days, then select ‘Calendar’ and choose a date. Then, in the indicator widget: Primary Value: (use the Fact_Orders table) Sum(Sales) Secondary Value: (use the Fact_Orders_LY table) Sum(Sales) Basically, in the primary value, the total sales for today’s date (from dashboard filter) is aggregated. Whereas in the secondary value, the date that used to join with the Fact_Orders_LY table is the equivalent date from the previous year, so we get the total sales from the last year, same day of the same week num aggregated automatically. Using this as the base logic, we could implement any use-case that involves a date to date mapping, however it might require the use of another mapping table and LOOKUP function to fetch the equivalent date for every date in the Dim_Date table.Maintain Percentage Contribution Using ALL Function
Analytical Need Even though this requirement sounds fairly simple, a lot of times, this use case gets confusing. If there are 10 stores for a Retail Chain, sometimes the Management would only want to compare 3 stores and split the entire contribution within the 3 retail stores. But there would be instances where the business would want to see the numbers of 3 selected stores but at the same time, would want to see the contribution of each store to the overall sales figure (sum of 10 stores). In the case of the latter, the selected three stores will be filtered, but the contribution percentage should not be altered only for the 3 stores, but it should be calculated with respect to all the stores of the Retail Chain. Mathematically, the denominator should be the same number (Total Sales across 10 stores) before and after selecting the 3 specific stores. Modeling Challenge The first case where the contribution percentage gets altered based on the filtered values, we could make use of the ‘Contribution’ function and achieve the same. Contribution function could be used from the Functions tab or by simply using the ‘Quick Functions’ of the Value menu. (or) But to achieve the latter use case, we cannot make use of the ‘CONTRIBUTION’ function but try working this out mathematically. Solution MATHEMATICAL SOLUTION: Irrespective of the filter applied on the Store, the percentage contribution should not change. So, Percentage Contribution of Store A = Total Sales $ of Store A / (Total Sales of All Stores), Percentage Contribution of Store B = Total Sales $ of Store B / (Total Sales of All Stores), and so on… To maintain the percentage contribution unchanged when filtering out few members, we need to have the denominator constant. SISENSE SOLUTION: We make use of the ALL function to create the denominator as mentioned in the documentation. This way, the widget filter to pick specific stores will not affect the percentage contribution value. So, depending on what the use case is, we could choose which formula needs to be utilized.How to Change the Pivot Header Row Style with TransformPivot
How to Change the Pivot Header Row Style with TransformPivot This solution is based on Pivot 2.0 API supported in Sisense version L8.2.1 or later (not supported in Sisense for Windows). For this solution, we shall use cellStyle from Pivot 2.0 API. The following options are available to adjust the cell style: Name Type Description fontSize number or string Text size fontWeight number or string Text weight fontStyle string Text style lineHeight string Text line height textAlign string Text alignment: 'left', 'right' or 'center' color string Text color backgroundColor string Cell background color padding number or string Cell padding borderWidth number Cell border width borderColor string Cell border color (with CSS fallback) Below I provide a widget script that identifies the first row in the pivot widget and utilizes transformPivot to replace the existing cell style with the new backgroundColor, color, and textAlign. Other cellStyle parameters can be set as in the example script. widget.transformPivot({}, (metadata, cell) => { if (metadata.rowIndex === 0) { cell.style = cell.style || {}; cell.style.backgroundColor = '#0057B7'; //sets cell background color cell.style.color = '#FFDD00'; //sets cell text color cell.style.textAlign = 'right'; //sets cell text align }; } ); To utilize the script, open the pivot widget in edit mode, use the three dots menu, and choose "Edit Script". Paste the script, save the changes, and refresh the dashboard. Enjoy the result! Disclaimer: Please note, that this blog post contains one possible custom workaround solution for users with similar use cases. We cannot guarantee that the custom code solution described in this post will work in every scenario or with every Sisense software version. As such, we strongly advise users to test solutions in their own environment prior to deploying them to ensure that the solutions proffered function as desired in their environment. For the avoidance of doubt, the content of this blog post is provided to you “as-is” and without warranty of any kind, express, implied or otherwise, including without limitation any warranty of security and or fitness for a particular purpose. The workaround solution described in this post incorporates custom coding which is outside the Sisense product development environment and is therefore not covered by not covered by Sisense warranty and support services.Control Chart
Please note this article is useful for Windows users and users who use older Linux versions that pre-date the Analytical Engine. Control charts are a common way to show a trend line of some metric, as compared to an upper and lower boundary. This article explains how to create a control chart in Sisense.1 Step 1 - Create a Line Chart with the metric & mean Create a standard line chart with time as the x-axis and pick a metric to use. For this example, machine temperature data was aggregated by day. Create the first measure as just the Avg Temp, to get a line chart that has Avg Temp per day. Then add a second measure that calculates the Avg Temp for all days. You can use a measured value to calculate the Avg Temp for ALL days, which will show a flat line across the chart. Star this formula as Avg Temp for All Days. Step 2 - Calculate the Standard Deviation In order to create the upper and lower bounds, we need to figure out the standard deviation of the daily temperature. Sisense has a built-in function for calculating the standard deviation, you need to pass in the x-axis dimension (days in date) followed by the metric (Avg Temp). Star this formula as Avg Temp St Dev. Step 3 - Create the Upper/Lower Bounds Now that we have the mean for all days and the standard deviation, we can calculate the upper and lower bounds. Create a new measure that adds the AvgTemp for All Days plus 2 x Avg Temp St Dev, and label it as Upper Bound. Create a similar measure that takes the Avg Temp for All Days and subtracts 2 x Avg Temp St Dev to get the Lower Bound. Now if you hide toggle visibility on the Avg Temp for All Days and Avg Temp St Dev measures, you will end up with a chart that has the Avg Temp by day along with an upper and lower bound. Step 4 (optional) - Auto-Adjust the y-Axis You may notice that Sisense will start the y-axis at 0 by default. If you want this to be calculated dynamically, add the below code using the Edit Script button of the widget. widget.on('ready', function(se, ev){ // Parameters var seriesIndex = 0; var adjustmentPercent = 0.25; // Figure out the min/max var min = null, max = null; $.each(se.queryResult.series[seriesIndex].data, function() { if (!min || min > this.y) { min = this.y; } if (!max || max < this.y) { max = this.y; } }) // adjust based on the delta var delta = max-min; max = max + (delta*adjustmentPercent); min = min - (delta*adjustmentPercent); // Set the min/max on the chart var e = element; var chart = e.data('hc'); chart.alignTicks =false; chart.yAxis[0].update({max:max, min: min}, true); }) References/Notes Control Chart Wiki Example Elasticube and Dashboard are attached below, the sample data came from a Hadoop Tutorial Sensor Data.ecdata ControlChart.dashIndicator Gauge as a Percentage
If you are using a gauge indicator in your dashboard and want to make the gauge reflect a percentage like shown below (elasticube is Sample Retail) - Follow the steps below - Steps: 1. Create an indicator widget and add a value you want to review. In this example, I want to get a percentage of sales order in the USA out of all sales orders: (COUNT([Sales Order ID]),[Region])/COUNT([SalesOrderID]) 2. Click on the "123" selector on the value and select percent and click OK 3. On the right hand side of the widget editor, click "Design" then "Gauge" Additional editor options on the left-hand side of the widget editor should appear. 4. Change the "min" and "max" values to percentages by clicking the "123" selector 5. For the max value, click the pencil icon then change the value from 100 to 1 Then click OK 6. Now you should have a correct looking gauge with a range of 0% to 100%
