Pivot2. How to set up styles with the scripts?
Currently, in Linux installation, you can face widget [Pivot2]. This is a new realization of the widget [Pivot]. This means that it has new styles, new classes, etc. Almost all the scripts to change styles of the resulting table used DOM selectors to find elements that will be changed. Since a structure of a new pivot differs from the previous realization, then scripts developed for the first implementation of the pivot are not applicable to the new one. Below, I will explain a common approach on how to change styles in Pivot2. First of all, now Pivot is not fully generated HTML code. Data are retrieved via WebSocket protocol and each pivot has three parts: Headers; Categories; Values. These parts are loaded independently but will be displayed as an entire pivot. The second - in a new pivot attribute [fidx=i] does not exist, so you cannot use this attribute as a selector to select some column. The third - values are displayed in HTML element <div> (not in <td> as it was before in the first version) New pivot structure: FORMATTING EXAMPLES 1. Highlight text in column 2 $("div[class*='--col-1'] div.table-grid__content__inner").css('backgroundColor','red’) $("td[class*='--col-1'] div.table-grid__content__inner") Two things are going on here: $("td[class*='--col-1’]”) // select any td cell with classname that contains --col-1 $(“x y”) // select x first, then within the x select the y. Specifically, find all cells with a classname that contains --col-1. Then, inside those, only select the div with the classname table-grid__content__inner 2. Highlight text + padding in column 2 $("div[class*='--col-1'] div.table-grid__content__inner").parent().css('backgroundColor','red') 3. Highlight all cells in column 2 $("div[class*='--col-1'] div.table-grid__content__inner").parent().parent().css('backgroundColor','red') < jQuery Tip > $("DIV[CLASS*='--COL-1'] DIV.TABLE-GRID__CONTENT__INNER") is the lowest level <div>, which contains the displayed text. .parent() – one level up – another <div>. .PARENT().PARENT() – (2 LEVELS UP) – THE CELL. 3 levels up, .parent().parent().parent() is the entire row. 4. Highlight entire rows of values $("div[class*='--col-1'] div.table-grid__content__inner").parent().parent().parent().css('backgroundColor','yellow') 5. Highlight the first dimension column $("td[class*='--col-0'] div.table-grid__content__inner").css('backgroundColor','yellow') 6. Highlight row 2 of the dimension (country) $("td[class*='--row-1']").css('backgroundColor','yellow') 7. Color Entire Rows Highlight the entire 7th row: $("[class*='--row-7']").css('backgroundColor','yellow') Color rows 2 and 3: $(".table-grid__row-1").css('backgroundColor','yellow'); $(".table-grid__row-2").css('backgroundColor','lightgray') 8. Highlighting Entire First Row Of Data If we try the same approach with the top row, we will end up grabbing the header and the data - two rows: $(".table-grid__row-0").css('backgroundColor','yellow') That is not what we want, so let's be more selective in our jQuery. After checking the DOM structure of the pivot, let’s only look for our rows under the div.multi-grid__bottom-part, which contains dims /categories (country names) $("div.multi-grid__bottom-part .table-grid__row-0").css('backgroundColor','yellow') < jQuery Tip > $("DIV.MULTI-GRID__BOTTOM-PART .TABLE-GRID__ROW-0") FIRST, FIND DIV.MULTI-GRID__BOTTOM-PART. THEN, WITHIN THAT, ELEMENTS OF CLASS .TABLE-GRID__ROW-0. GENERALLY: $(“X Y“) – FIND Y WITHIN X Plus also get the top row of the values: $("div.scroll-elem .table-grid__row-0").css('backgroundColor','yellow') Now, let's combine these two separate jQuery selectors: $("div.scroll-elem .table-grid__row-0, div.multi-grid__bottom-part .table-grid__row-0").css('backgroundColor','yellow') < jQuery Tip > $("DIV.SCROLL-ELEM .TABLE-GRID__ROW-0, DIV.MULTI-GRID__BOTTOM-PART .TABLE-GRID__ROW-0") TWO THINGS ARE GOING ON HERE: $(“A B, C D”) // SELECT $(“A B”), ALSO SELECT $(“C D”), COMBINE THE RESULT $(“X Y”) // SELECT EVERY X, THEN SELECT EVERY Y INSIDE THE X CONDITIONAL FORMATTING 9. Let's turn yellow every number greater than 1000 in column 2: $("div[class*='--col-1'] div.table-grid__content__inner").each(function(index, item) {var myItemNum = parseFloat($(item).text() .replace(',','').replace('$','')); if (myItemNum > 1000) {$(item).parent().css('backgroundColor','yellow') }; }); 10. Now, same, but let's color the entire cell (not just its text): $("div[class*='--col-1'] div.table-grid__content__inner") .each(function(index, item) { var myItemNum = parseFloat($(item).text().replace(',','').replace('$','')); if (myItemNum > 1000) {$(item).parent().parent().css('backgroundColor','yellow')}; }); 11. Now, for every item > 1000 in column 2, let's color the entire row of values: $("div[class*='--col-1'] div.table-grid__content__inner") .each(function(index, item) { var myItemNum = parseFloat($(item).text().replace(',','').replace('$','')); if (myItemNum > 1000) {$(item).parent().parent().parent().css('backgroundColor','yellow’)}; }); 12. Now, for each of the values > 1000, let's color the entire row, including values and the country dimension This is more difficult because there is no way to find the right text that's > 1000, then navigate from that cell to the corresponding row of the country (the way we used to do this in Pivot 1), because now they exist in different table structures. $("div[class*='--col-1'] div.table-grid__content__inner").each(function(index, item) { var myItemNum = parseFloat($(item).text().replace(',', '').replace('$', '')); var myRowNum; var myDimSelector = ""; if (myItemNum > 1000) { $(item).parent().parent().parent().css('backgroundColor', 'yellow'); // row of values myRowNum = parseInt($(item).parent().parent().parent().attr('class').slice(-1)) + 1; myDimSelector = "td.table-grid__cell--row-" + myRowNum; $(myDimSelector).css('backgroundColor', 'lightgreen'); //row of dimension headers }; }); Explanation: To better understand this, let’s do this step by step. $("div[class*='--col-1'] div.table-grid__content__inner").each(function(index, item) { var myItemNum = parseFloat($(item).text().replace(',','').replace('$','')); if (myItemNum > 1000) {$(item).parent().parent().parent().css('backgroundColor','yellow') }; }); This returns 3 rows that match, each row is like this: div.table-grid__row.table-grid_row-1 $("div[class*='--col-1'] div.table-grid__content__inner").each(function(index, item) { var myItemNum = parseFloat($(item).text().replace(',','').replace('$','')); if (myItemNum > 1000) {console.log($(item).parent().parent().parent() ) }; }); The jQuery we want to execute is this: $("td.table-grid__cell--row-1").css('backgroundColor’,’lightgreen’) , but where row-1 is replaced with the current value of the row Running: $("div[class*='--col-1'] div.table-grid__content__inner").each(function(index, item) { var myItemNum = parseFloat($(item).text().replace(',','').replace('$','')); if (myItemNum > 1000) {console.log($(item).parent().parent().parent().attr('class') ) }; }); Returns: table-grid__row table-grid__row-1 table-grid__row table-grid__row-3 table-grid__row table-grid__row-5 Let's grab 1 character from the right using slice(-1) (1 char from the right). FINAL SCRIPT: $("div[class*='--col-1'] div.table-grid__content__inner").each(function(index, item) { var myItemNum = parseFloat($(item).text().replace(',', '').replace('$', '')); var myRowNum; var myDimSelector = ""; if (myItemNum > 1000) { $(item).parent().parent().parent().css('backgroundColor', 'yellow'); // row of values myRowNum = parseInt($(item).parent().parent().parent().attr('class').slice(-1)) + 1; myDimSelector = "td.table-grid__cell--row-" + myRowNum; $(myDimSelector).css('backgroundColor', 'lightgreen'); //row of dimension headers }; });Widget Script: Stream Data Into A Widget With Sub-Second Refresh
Challenge The utility of a sub-second refresh rate is limited to monitoring use-cases. This, of course, is not BI, but there may be cases where a BI use-case also incorporates live streaming data to trigger an action or decision. An example is if there is a power surge, a worker may want to immediately turn off the flow of electricity and then use BI to analyze what the cause of the surge was. The challenge is presented when we try to query large bodies of data and ingest them with a dashboard quickly. With data sources like memSQL, it is certainly possible to query and display 100K records every second, but this requires a specific set of tools and configurations. Solution As a general user with a generic MSQL database but perhaps with a REST endpoint attached to IOT devices, it is certainly easier to make a query for the latest point of data and reflect this in the dashboard. This is precicely what we will do. Creating The Base Widget: This widget will need base data to create a Sisense widget. Once the widget is created we can apply a script to feed in live values. There are three requirements to the base widget you should consider: How many values do you want to see in your chart The value the base widget will show before new values are passed in BASE VALUES It’s best to set the widget up with values that align with the data you will query. If you do not have data in your EC that corresponds with the live data you can feed in a hardcoded 0 into the value panel. NUMBER OF VALUES INSIDE THE LIVE CHART The number of values will stay consistent as more values are populated. As new values are populated, old values are dropped. Set the initial range of values by applying a filter to the X-axis and specify that you want to see the latest X units. In the example above, the chart will always display 30 units. Setting The Widget Script This script will do the following: Query a REST endpoint every X milliseconds Push the result into a line chart Update the Sisense Chart Copy and paste the widget script below into a widget script. Save the script, refresh the widget, and apply it. I recommend creating new functions for every widget you apply this on to avoid function conflicts. COMPLETE CODE WITH COMMENTS widget.on('domready', () => { asyncCallForDeviceA(); }) var getNewDataPointForDeviceA = function(){ // assign a value to a variable "quit" in the console to stop getting new values if (typeof quit == "undefined"){ var newValue; var newTime; setTimeout( () => { //Get data with REST API $.getJSON( 'https://api.exchangeratesapi.io/latest', function( json ) { //get value newValue = json.rates.AUD; //get time at which the data was retrieved newTime = new Date().toString().slice(4,24); //update the widget data widget.rawQueryResult.values.push( [ {data:String(newTime), text:String(newTime)}, {data:newValue, text:String(newValue)} ] ); //drop the first value widget.rawQueryResult.values.shift() //redraw the widget with the new value widget.redraw(); }); }, 800); //800 means this function will occur every 800 milliseconds } } var asyncCallForDeviceA = async function(){ await getNewDataPointForDeviceA(); } 6 Post actionsHow 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!Filter For Last Working Day
When a filter based on this custom column is active, the dashboard will reference the last working day. If the dashboard is checked on Monday or Sunday, Friday's data will be shown. If the dashboard is checked on Tuesday - Saturday, the prior day will be shown. Apply this SQL script to a custom column of a DateDim table and apply a filter based on this new column to the dashboard set to 1. ifint( [Date] = case when DayOfWeek(CurrentDate()) in (2,3,4,5,6) then adddays(createdate(getyear(now()), getmonth(now()), getday(now())), -1) -- if the day is between Tuesday and Saturday go back return yesterday when DayOfWeek(CurrentDate()) = 1 then adddays(createdate(getyear(now()), getmonth(now()), getday(now())), -3) -- if the day is a Monday go back 3 days for last friday when DayOfWeek(CurrentDate()) = 7 then adddays(createdate(getyear(now()), getmonth(now()), getday(now())), -2) -- if the day is a Sunday go back 2 days for last friday end , 1, 0)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.Divergent/Negative Bar Chart
Sometimes you may want to compare two values alongside each other to measure impact of one vs the other but without stacking them or having them side by side. This is often called a divergent bar chart. This can be done in Sisense with just a little bit of work. First take your left measure and multiply it by -1 to make it negative, you will want to sort ascending on that measure. This gives us almost what we want, but the tooltips and the axis show negative values which can be misleading! So let's take care of those! The axis label We can easily extend the usual sisense formatter to replace its output when it contains a "-" sign. widget.on('beforeviewloaded',function(se,ev){ old=ev.options.yAxis[0].labels.formatter ev.options.yAxis[0].labels.formatter= function () { return old.apply(this).replace("-","") } }) The tooltip Again here we can use the "beforedatapointtooltip" event to access the tooltip context each time and replace the "-" in the tooltip. widget.on("beforedatapointtooltip", function (se, args){ args.context.points[0].value=args.context.points[0].value.replace("-", "") })Calculate the Month Difference Between Min and Max Date Fields from the Dashboard (without scripting)
Analytical Need For a metric, I need to calculate the month difference between the min and max date of purchase for each brand in my dataset. I want the dates to be dynamic in respect to filtering, etc. I am using the attached .ecdata and .dash files for this solution. See this article on importing a .dash file, and this on importing an .sdata file. Challenge The MIN() and MX() dashboard functions do not work on date fields. And even if we create a Numeric Representation Of Date Fields, a simple subtraction of the results won't work because we can't just use the difference in days (think of the case where the dates are two days apart, but land in different months). How can we identify the earliest and latest date and calculate the month difference between them? Elasticube Prep(Step 1) Data Requirements In the Elasticube, create a custom field called [Date Numeric] to convert your date field to an integer using 10000*getyear(Date)+100*getmonth(Date)+getday(Date) Create this field in the fact table since it is a value we will be performing a calculation on. Dashboarding Solution (Step 2) We need to extract the year and month values from our [Date Numeric] field in order to calculate the month difference between to date values. To do this, we will reverse the logic used to convert the field to an integer. The formula syntax below achieves this for all three components of the earliest date. To generate the year, month and day corresponding to the latest date value, we can swap out MIN for MAX. Year of MIN Date Integer: FLOOR(min([Date Numeric])/10000) Return the Month of MIN Date: FLOOR((min([Date Numeric])-(10000*(FLOOR(min([Date Numeric])/10000))))/100) Return the Day of MIN Date: (min([Date Numeric])-(10000*(FLOOR(min([Date Numeric])/10000))))-(100*(FLOOR((min([Date Numeric])-(10000*FLOOR(min([Date Numeric])/10000)))/100))) For this specific use case, we want to get the month difference between the min and max: Final Solution - Month Difference Formula: ((FLOOR(max([Date Numeric])/10000) - FLOOR(min([Date Numeric])/10000) )*12) + (floor((max([Date Numeric])-(10000*(FLOOR(max([Date Numeric])/10000))))/100) - floor((min([Date Numeric])-(10000*(FLOOR(min([Date Numeric])/10000))))/100)) ((FLOOR(max([Date Numeric])/10000) - FLOOR(min([Date Numeric])/10000) )*12) + (floor((max([Date Numeric])-(10000*(FLOOR(max([Date Numeric])/10000))))/100) - floor((min([Date Numeric])-(10000*(FLOOR(min([Date Numeric])/10000))))/100))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 } }Week over week analysis with custom fiscal year: Use case of a fuel and convenience retail operator
Introduction Week-over-week (WoW) analysis is a key part of performance tracking for fast-moving, high-traffic businesses such as convenience stores, gas stations, and car washes. For these organizations, aligning the fiscal calendar with operational cycles rather than the standard calendar year makes reporting more meaningful. In this use case, the fiscal year begins on the closest Sunday to January 1st, ensuring each year starts with a full week. This structure simplifies weekly reporting and keeps week-to-week comparisons consistent across years, which is important for tracking trends like fuel sales, store traffic, and service volumes. While nonstandard, this setup is commonly used in practice. What the Solution Does For standard, fixed calendar or fiscal years, week-over-week analysis can be achieved using the “First Month of Fiscal Calendar” and “First Day of Week” settings, along with the PASTYEAR function. However, for dynamic fiscal years that begin on a weekday closest to January 1st, these features don’t provide a usable solution, since the start date can fall in the previous or following calendar year. The solution uses the Filtered Measure certified add-on and a custom dashboard script to handle the custom fiscal year. Two year filters are added to the dashboard: one represents the selected fiscal year (user-selectable), and the other represents the prior year for comparison (locked and optionally hidden), which is automatically set with a dashboard script. The Filtered Measure plugin applies the selected-year filter to the measure for the chosen period, while the prior-year filter applies to the measure for the corresponding period in the previous year. This approach ensures that week-over-week calculations respect the custom fiscal calendar, providing accurate comparisons across equivalent weeks. Note: In this particular implementation, the fiscal years and week numbers are pre-calculated in the database and stored as numeric columns. To create a Date dimension table in your Elasticube with fiscal years starting on the first Sunday closest to January 1st, refer to the SQL example below. Why It’s Useful This solution addresses the native functional limitation by respecting the custom fiscal calendar, ensuring weekly trends are comparable across years. As a result, teams can reliably track key metrics, such as fuel sales, store traffic, and service volumes, on a true week-by-week basis, supporting better operational planning and more informed decision-making. Attachments WeekoverWeekAnalysiswithCustomFiscalYear.dash.txt (dashboard) Sample ECommerce - Custom Fiscal Year.smodel.txt (elasticube) JS Script - Automatic Update for Second Year Filter.txt (dashboard script) SQL Query - Dim Date with Custom Fiscal Year.txt (custom table SQL query) For the script to hide the second filter, refer to this BINextLevel article: Hide dashboard filters. Note: remove the .txt extension before importing the dashboard (.dash) and the Elasticube (.smodel) files.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.
