How to pivot my data and get counts based on the min and max date value
Morning Sisense community, I need to pivot my data and get counts based on the min and max date value. Given the below data: row date count Example 1/01/2025 35 Example 2 1/01/2025 20 Example 1/02/2025 35 Example 2 1/02/2025 10 Example 1/04/2025 34 Example 2 1/04/2025 10 Example 1/08/2025 34 Example 2 1/08/2025 10 I want to pivot it to: Row Start Count End Count Example 35 34 Example 2 20 10 I need to be able to filter my data in the dashboard. So, if the date is filter to “1/04/2025” to “1/08/2025” in the dashboard, the pivot needs to update to: Row Start Count End Count Example 34 34 Example 2 10 10 How would I achieve this?
Pivot 2.0 - Manipulating a Pivot Chart
Pivot 2.0 - Manipulating a Pivot Chart Introduction The following article discusses how to manipulate the data and styling of your Pivot 2.0 widget. Please refer to the following article first: https://sisense.dev/guides/customJs/jsApiRef/widgetClass/pivot2.html. Cell Identification To manipulate a pivot cell, we'll have to learn the different identifiers of each cell in the table. Cell Types Each cell is linked with a 'type' that represents the data it contains: A member cell refers to a Column/Row header A value cell refers to a table value cell A subtotal cell refers to a subtitle row (title + values) A grandtotal cell refers to a grand-total rows & columns (titles + values) A cell may have more than one 'type': A cell that has a subtotal and a member type represents the subtitle row title A cell that has a subtotal and a value type represents the subtitle row values (including values + column grand total values) A cell that has a grandtotal' and a member type represents the grand-total row and column titles A cell that has a grandtotal' and a value type represents the grand-total values (including row & column grand total values) A cell that has a grandtotal, a subtotal, and a value type represents the grand total values in the subtitle rows See the following pivot table and the corresponding cell types Original Table Cell Types Manipulating a Cell (Based on its Type) Here are two examples of how to manipulate a cell based on its type: widget.transformPivot( { type: ['value'] }, function (metadata, cell) { // Manuipulation code } ); widget.transformPivot( {}, function (metadata, cell) { if (metadata.type.includes('value')) { // Manuipulation code } } ); H2 - Cell Indexes Each cell is represented by three indexes: Metadata Index - Representing the logical column ID in the table (aligns with the selected rows/values/columns) Column Index - Representing the column number in the table Row Index - Representing the row number in the table Metadata Index See the following pivot table, the pivot configuration pane, and the corresponding metadata indexes: Original Table Metadata Index Here is an example of how to manipulate a cell based on its metadata index: widget.transformPivot( {}, function (metadata, cell) { if (metadata.index == 1) { // Manuipulation code } } ); Column/Row Index See the following pivot table and the corresponding row/column indexes Original Table Row/Column Indexes Here is an example of how to manipulate a cell based on its columns/row index: widget.transformPivot( {}, function (metadata, cell) { if (metadata.colIndex == 3 && metadata.rowIndex == 2) { // Manuipulation code } } ); Cell Row/Column/Measure Name Each cell may be affiliated with three metadata values: Measure - The measure calculated in this cell (name & formula) Column(s) - The column(s) this cell is under (field, title, & value) Row(s) - The rows(s) this cell belongs to (field, title, & value) Manipulating a Cell (Based on their Measure) Here is an example of how to manipulate a value cell based on the measure's name: widget.transformPivot( {}, function (metadata, cell) { if (metadata.measure.title === 'SUM') { // Manuipulation code } } ); Manipulating a Cell (Based on Their Row) Here is an example of how to manipulate a value cell based on the row's value: widget.transformPivot( {}, function (metadata, cell) { // Format based on the value of the a row's name and value metadata.rows.forEach(function(row) { if (row.title === 'Year' && row.member === '2012-01-01T00:00:00.000') { // Manuipulation code } }) } ); Manipulating a Cell (Based on Their Column) Here is an example of how to manipulate a value cell based on the column's value: widget.transformPivot( {}, function (metadata, cell) { // Format based on the value of the a row's name and value metadata.columns.forEach(function(column) { if (column.title === 'Online' && column.member === 'False') { // Manuipulation code } }) } ); Cell Manipulation The possible manipulation options of a cell include: value - Raw value of the cell from query response (manipulating this value is useless) content - The HTML contents of this cell style - The cell formatting Here is an example of how to manipulate value cells' style: widget.transformPivot( {}, function (metadata, cell) { cell.style = { backgroundColor : 'lightgray', fontSize : 14, fontWeight : 'bold', fontStyle : 'italic', textAlign : 'center', color : 'black', borderColor : 'black', borderWidth : '3px', minWidth : '150px', maxWidth : '200px' }; } ); Here is an example of how to manipulate value cells' value: widget.transformPivot( {}, function (metadata, cell) { if (cell.content == '') cell.content = '---' } ); Check out this related contentChange the background color of the Pivot cell on value Windows
earn how to customize the background color of Pivot cells based on their values in Windows. This guide provides a simple JavaScript script that changes cell colors for categories like 'Male', 'Female', and 'Unspecified'. Enhance your Pivot widget by following easy steps to apply the script, ensuring your data visualization is not only informative but also visually appealing. Perfect for anyone looking to add a personal touch to their Pivot tables!Usage PivotAPI to Beautify Data On Pivot
Usage PivotAPI to Beautify Data On Pivot In this article, we will review the capabilities of PivotAPI to customize cells. Additionally, we will review important properties, as well as some tricks, we can use to visualize data in a more beautiful way. NOTE: Usage of these technics requires minimal knowledge of JavaScript We will review data formatting for two use cases: Changing styles of the cell depending on the value in the cell; Converting seconds to HH:MM:SS format. Changing styles of the cell depending on the value in the cell Code: const myTarget = { type: ['value'], values: [ { title: 'formulaTitle' // put here desired column } ] }; widget.transformPivot(myTarget, (metadata, cell) => { //Exclude 0 and empty rows const isPositive = cell.value > 0; const isNegative = cell.value < 0; cell.style = cell.style || {}; let additionalStyle; if (isPositive) { cell.content = `${cell.content} ↑`; additionalStyle = { color: 'green', fontWeight: 'bold' }; } else if (isNegative) { cell.content = `(${cell.content.replace('-', '')}) ↓`; additionalStyle = { color: 'red', fontSize: '18px' }; } if (additionalStyle) { cell.style = Object.assign(cell.style, additionalStyle); } }); Explanation: We need to define columns where the script will be applied. It's done in the variable [myTarget]. This variable has two properties: type - refers to the type of cells, which will be modified by the script; values - an array of the values, which will be modified by the script. So, our script is targeted to update cells, which shows values computed by the formula 'formulaTitle'. Once we have defined the target of the script, we will need to initiate widget.transformPivot(). As the first argument we are passing the target and as the second argument we send a callback function, that will be executed for the cells. The callback function receives information about the cell as the second argument. It contains several important properties: value - this is a numeric value before formatting (for formulas only); content - this is formatted value, which is shown in a frame; style - additional styles of the cell. In terms of our script, we did the following: Check the value in the cell to understand its sign; After this, depending on the sign we update the styles of the cell and change content by adding an arrow up or arrow down. Before After Converting seconds to HH:MM:SS format. Code: const formulaTitle = 'Formatted'; //Name of the formula, which store integer; const time_separator = ":"; //Delimiter const hourSign = ''; //Symbol for hours if needed const minuteSign = ''; //Symbol for minutes if needed const secondSign = ''; //Symbol for seconds if needed widget.transformPivot( { type: ['value'] //We are going to process values (formulas) only }, processCell ); function processCell(metadata, cell) { if (metadata.measure.title === formulaTitle) { //Find formula with the name defined in a variable [formulaTitle] try { cell.content = computeContent(cell); //Convert value into desired format } catch(err) { console.warn('Unable to process cell'); } }; } function computeContent(cell) { if (!cell.value || isNaN(parseInt(cell.value))) { return cell.content; } else { const value = parseInt(cell.value); const sign = value < 0 ? "-" : ""; const hours = parseInt(value / 3600); const minutes = parseInt((value - (hours * 3600)) / 60); const seconds = value - (hours * 3600) - (minutes * 60); const hoursText = `${hours < 10 ? "0" + hours : hours}${hourSign}`; const minutesText = `${minutes < 10 ? "0" + minutes : minutes}${minuteSign}`; const secondsText = `${seconds < 10 ? "0" + seconds : seconds}${secondSign}`; return `${sign}${hoursText}${time_separator}${minutesText}${time_separator}${secondsText}`; } } Explanation: This script processes all the cells, which are produced by the formulas. If you have multiple formulas in your pivot, then the time of execution can be quite long. If you want to process some particular cells, then check the first example - it shows how to limit columns, which are processed by the script. We are going to process only values defined by the formula with the title 'Formatted', so we add this condition to the function callback. If this condition is not met (another formula computed the value), we will not execute the further logic. The further logic converts an integer from the cell to the format HH:MM:SS. The computed value is returned and set as [cell.content]. Result of execution (original value is stored in the column [Original], the formatted one in the column [Formatted]): I hope you find this article useful and leverage the knowledge shared about PivotAPI capabilities and their usage. Please share your experience in the comments!
