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.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.Ordering My Pie Chart From Largest Percentage To Smallest
Modeling Need: My pie chart is ordering the data in alphabetical order by category. I am looking to order the data from largest to smallest percentage. Solution: I can change the widget type from pie chart to column chart. Then order the values in descending order: Then change the chart type back to pie chart to see the representation I am looking for:Compare 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 }; }); } });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!Dynamic Pivot Table Data Bar Color Formatting
Introduction This article describes how to dynamically color the Data Bars within a pivot table based on the cell values. Presented here are two methods: Use stock gradients to provide different colors based on cell values Define RGB shade magnitudes based on cell values Examples Method 1 Method 2 Implementation Steps Method 1 Utilizing D3.JS Sequential and Diverging Color Scales This method uses D3 version 5's interpolate methods to select a color based on the bar width. The "d3.interpolate<color code>" method accepts a float value between 0 and 1 and maps the number to a color within a selected gradient. A complete list of gradients can be found here under Diverging and Sequential: https://github.com/d3/d3-scale-chromatic Steps Navigate to Advanced Widget View Copy the snippet below into the Widget custom JavaScript editor to see the results above. Save the code Refresh the Widget Advanced Editor Page Apply Changes Editing Lightness and Darkness of the Gradient In this snippet example the range highlighted in green refers to the entire range of the gradient, as 0 represents the start of the gradient, and 1 the end. However often times, you will not want the minimum value represented by 0, because it is too light. The solution is to replace 0 with a slightly higher value, such as 0.2, until you are satisfied with the shade. Editing Color of the Gradient In this snippet example the method highlighted in yellow controls the color. To change which color gradient is used, navigate Here and select a d3.interpolate method of a color range you like. Replace the gradient for positive values, and negative values. If you choose a diverging gradient method, be mindful that you will also need to create two scales, one from 0-.5, and .5 to 1 for negative and positive values respectively Paste This Snippet into Widget JavaScript Editor widget.on('ready', function(sender, ev) { console.log('starting custom code'); // Update D3 to version 5 to allow use of interpolation $.getScript('https://d3js.org/d3.v5.min.js', function(data, textStatus) { return true }); // Convert percentage range to 0 to 1 or any range in between. myScale = d3.scaleLinear().domain([0, 100]).range([0, 1]); /********************************************** Color bars red or green for positive/negative values. **********************************************/ //get an array of bar elements var MyBars = $('.bar', element); //find how many bars are visible var L = MyBars.length; //init variables var MyBarSign; var MyBar; //loop through each bar for (i = 0; i < L; i++) { //gets bar width and converts number to a float value var PercentStringWidth = MyBars[i].style.width; var IntegerStringWidth = PercentStringWidth.slice(0, -1); var FloatWidth = parseFloat(IntegerStringWidth); //apply scale function to the bar width var ScaledWidth = myScale(FloatWidth); //look at a specific bar MyBar = MyBars[i]; //get the class name MyBarSign = MyBar.parentNode.className.substring(0, 3); //if the class name starts w/ 'pos', color it using the green gradient. Otherwise color it using the red gradient. if (MyBarSign == 'pos') { MyBar.style.backgroundColor = d3.interpolateYlGn(ScaledWidth); } else { MyBar.style.backgroundColor = d3.interpolateYlOrRd(ScaledWidth); } } }); Method 2 Creating Self-Defined RGB Color Formulas This method takes advantage of D3.JS's scaleLinear domain and range functions. The method converts a percentage to a scale that the RGB color values are based on - 0 to 255. For example, 50% is converted to 127.5, or half brightness of a red, green, or blue value. The background color is calculated by converting the width value to a RGB value. Steps Navigate to Advanced Widget View Copy the snippet below into the Widget custom JavaScript editor to see the results above. Save the code Refresh the Widget Advanced Editor Page Apply Changes Editing Lightness and Darkness and Color of the Gradient In this snippet we are using the RGB values 0 - 255. High values represent lighter tones and lower values darker tones. You can define a function for how the color changes depending on how the width changes, as seen in the yellow highighted text. When using division remember to wrap the function with ParseInt as RGB values cannot be floats. To select RGB colors you can Google "RGBcolor picker" and use the color picker widget, or use this W3schools page. Paste This Snippet into Widget JavaScript Editor widget.on('ready', function(sender, ev) { console.log('starting custom code'); myScale = d3.scale.linear().domain([0, 100]).range([0, 255]); /********************************************** Color bars red or green for positive/negative values. **********************************************/ //get an array of bar elements var MyBars = $('.bar', element); //find how many bars are visible var L = MyBars.length; //init variables var MyBarSign; var MyBar; //console.log(MyBars); //loop through each bar for (i = 0; i < L; i++) { //gets bar width var PercentStringWidth = MyBars[i].style.width; var IntegerStringWidth = PercentStringWidth.slice(0, -1); var FloatWidth = parseFloat(IntegerStringWidth); //Convert to a 0 - 255 scale var ScaledWidth = parseInt(myScale(FloatWidth)); //look at a specific bar MyBar = MyBars[i]; //get the class name MyBarSign = MyBar.parentNode.className.substring(0, 3); //if the class name starts w/ 'pos', color it green. Otherwise color it red if (MyBarSign == 'pos') { MyBar.style.backgroundColor = 'rgb(' + parseInt(ScaledWidth / 3) + ',' + ScaledWidth + ', ' + 200 + ')'; } else { MyBar.style.backgroundColor = 'rgb(' + ScaledWidth + ',' + parseInt(ScaledWidth / 3) + ', ' + 200 + ')'; } } });Show Top N Members Over All Groups Always Including Selected Member
Notice how Denmark's data above is significantly lower than the other country's. In spite of not falling into the selected top 10 range, it will still show on the chart for comparison purposes. Steps To Implementation Preparation Duplicate the Break By Dimension Column in the EC. In our case we select the country with "Country" but apply a break by on the duplicated column "Country2". It's the same data. Create an integer ID custom column / SQL column of your dimension. The country in my case. You can use rankdenseasc(Country) for this. Download and install the "Measured Filter" plugin from the Sisense Marketplace (Free) Set filter on the dashboard based on the original break-by dimension. In the case above, it is "Country". note, we are NOT using "Country2". 1. Setting Up The Widget Group, Values, Break By Items: Set this to the Rows/X-Axis grouping dimension Break by: Set this to the duplicated column of your break by dimension Values: Create a function and call it top x Add the function below where Max CountryRank is the Max() function around your dimension's integer ID @country is the filtered measure configured to the dimension filter of your dashboard and 999999999 is a value that is larger than any other values (this always puts it into the top N) "Total CONFIRMED" is your desired aggregation. This can be AVG(revenue) for example. ALL(DAYS) is the ALL function around your rows/X-axis dimension case when( [Max CountryRank] , [@country] ) = [Max CountryRank] then 999999999 else ( [Total CONFIRMED] ,ALL([DAYS])) end 2. Set The Top N Widget Filter 3. Display Your Desired Aggregation Disable top x by clicking the slider Values 2 of 2: Add new value and enter your desired aggregation. This should be the same as the ELSE aggregation in step 1 Optional: Format The Color Of The Selected Dimension Add this widget script to the widget: The filter that selects the dimension must be the first filter in the filter panel. // SET THE COLOR TO FORMAT THE SELECTED DIMENSION var formatColor = '#FC312F' widget.on("processresult", function(w, args){ var selectedDim = w.dashboard.filters.$$items[0].jaql.filter.members[0] args.result.series.filter(i=>i.name==selectedDim)[0].color= formatColor })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.dashFormatting Date And Time As A Single String
Caveats If you have windows, the only way to do this in a way that will show up in exports is to create a string representation of the date in the elasticube and use that. There are limitations. If these are significant, look at a move to Linux. This solution and all solutions besides just creating a string in the elasticube will require scripting. Designers may not understand how to do this. If you are on Linux 8.2.1+ the pivot2 api will allow widget scripts to also show up on pdf exports. This solution is applicable only to pivot tables in Linux 8.2.1+ Step 1 In your model, create a custom field that is seconds from epoch. In Sisense you can do this with a custom column like: Step 2 Use a widget script to format the date time Script source: https://github.com/nathangiusti/Sisense/blob/master/JS%20Scripts/Pivot2/EpochToDateString.js Results Before After CheersCalculate Weekly Overtime Using The Formula Editor And Its Functions
Analytical Need A lot of business use cases revolve around utilization of resources and their productivity where the resources could be their inventory, machines, carrier or the human resources of an organization i.e., employees. That’s when monitoring the weekly hours of employees comes in very handy and more importantly to keep track of the weekly overtime per employee as well as for the whole team/department/organization. Modeling Challenge Whenever the employee working hours data is captured, it is done on a daily basis and a lot of times we see that the employees split their daily hours between different job types or different facilities or different natures of ticket or different customers so that the capture of data is done at the most granular level. In order to measure the overtime, we need to first calculate the hours worked per week per Employee and then compute the overtime per Employee before we sum it up to find out the overtime of all employees for that week. If we pre-aggregate the weekly hours per employee, then we would lose out on filtering ability by the teams or job types or tickets which would prevent us from computing the overtime hours per facility/job type/nature of ticket against their own threshold. Most of the companies may have a common threshold of 40 hours / week for full-time employees overall but there could also be other thresholds e.g., facility-wise threshold of 10 hours per facility. Aggregated tables will eliminate the dynamic slicing of the data hence, then we need to use the employee hours table at the most granular level (per day hours split into each per facility/job type/nature of ticket) and make use of the front-end capabilities to compute the overtime hours per employee per day Solution We are going to consider a simple example to understand the solution: Here is a simple table that shows the hours of work logged by two different employees. Let us assume that the threshold for overtime is 40 hours per week as Abraham and Bob are full-time employees. So, if we have to find the overall overtime (both these employees combined), we cannot just sum the total hours of the 2 employees and subtract (40*2=) 80 to get the overtime, which will be like, 80 – 80 = 0 OVERTIME HOURS The above calculation is clearly incorrect as we could simply eyeball and tell that Abraham has worked less than 40 hours that week and Bob has worked 1.5 hours overtime which would result in 1.5 total overtime hours when both these employees combined. In order to compute this correctly, we need to compute the following by each employee: Add the working hours separately, then subtract 40 from that, and then if the result is positive, we need to add that to the overall overtime hours. If we are doing that on an indicator widget where the context of ‘Employee Name’ is not available unlike the pivot table, this could be possible by injecting the context of ‘Employee Name’ while computing the overall overtime. To inject a context (group by), we need to use the concept of multi-pass aggregation. As a part of the first step, create the simple overtime formula and star it as ‘Overtime’ using the CASE WHEN statement of the formula editor and visualize that in a pivot table with Employee Name in the rows: Now, let us use multi-pass aggregation and inject the context of Employee Name into the calculation of Overall Overtime using the saved formula ‘Overtime’. Create that in an indicator widget: Concept: We need to compute this ‘Overtime’ for each Employee and then Sum all of Overtime hours to get the Overall Overtime. Formula: Then, you will look at the correct value: In contrast, if you just use the saved formula ‘Overtime’ in the indicator widget without the context, you will end up seeing incorrect value: Since we are already filtering by a certain week at the dashboard level, we only have to inject the context of employees (have used Emp_ID) to compute the overtime per employee and then finally add it. The important part of the entire process is using the right context and aggregating another sum. If you look closely, the above was an example of Sum of Sum – Sum of hours per employee where (Sum of Hours – 40) if the employee has any overtime hours that week => Sum ( [Employee], Sum). If we could understand how this simple example works, then we could apply this and scale the usage of injecting context to implement any complex use case – this way we could eliminate the approach of pre-aggregated tables in the Elasticube but perform dynamic analysis using the frontend formula editor.