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.
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 actions
