cancel
Showing results for 
Search instead for 
Did you mean: 

How to pivot this data table?

lalasisi
9 - Travel Pro
9 - Travel Pro

I have this data table in my database. Both seller and contract terms are varchar fields and the rest are float fields.

lalasisi_0-1720644027305.png

I want to pivot this to show the end table like below. 

lalasisi_1-1720644071551.png

However, it seems impossible to do in sisense. I used pivot table in sisense and this is what I could do best, which is not acceptable by my manager. 

lalasisi_2-1720644306423.png

Below is my settings in sisense using pivot table. I have tried using table which didn't work either. I have read almost everything about pivot tables in the sisense community but nothing seems to help. Can any sisense gurus help with this? 

lalasisi_3-1720644481651.png

12 REPLIES 12

lee_hub
7 - Data Storage
7 - Data Storage

Thank you @lalasisi for this post. I too, am experiencing a similar problem. I think it is the default nature of pivot tables, regardless of technology, to not allow non-aggregated values in the Values section of a pivot. Hoping someone can provide a method, maybe it is JavaScript or something to force non-text values into the proper section of your pivot.

HamzaJ
12 - Data Integration
12 - Data Integration

Hey @lalasisi 

Sisense does not support this natively. You could create a table on a elasticube level and do the wanted aggregation there. You can also take a look at the Financial Table plugin from @Benji_PaldiTeam Paldi Solutions. This looks similar to what you are trying to do now

Hamza

HamzaJ_0-1720677298308.png

 

Thank you @HamzaJ 

@lalasisi  and @lee_hub , feel free to reach out if you'de to explore this option further 

[email protected] 

 

Thanks,

Ravid

Paldi Solutions - Gold Certified Partner

lalasisi
9 - Travel Pro
9 - Travel Pro

Hi @HamzaJ and @Ravid_PaldiTeam,

Thank you for your reply. The financial table plugin looks promising but I doubt if it will also work on displaying text fields. How would the plugin results look like when my data table contains both numbers that can be aggregated and text fields that can't be aggregated?

lalasisi 

It would be great if sisense allows drag and drop table cells where it supports any table format, structure and data type, just like tableau. Does anyone know how to propose feature to sisense development?

Hi @lalasisi 

 

The best way to propose features to Sisense is via their Product Feedback forum

Per your question about combining the abiltiy to combine texts and aggreagated data - this is a comemrcial solution. It can do it all and much more as it was desgined from the start to be a comperhansive solution to all scenarios where the simple out of the box table/pivot widget is not sufficient. 

We can hop on a quick call and I'll show you how it works. Its really simple and we offer free trials to all our plugins so you can just try it on your server to verify that it fit to your use case  (: 

Another option would be to customize your own solution using BloX.

The guys from RapidBI had pasted a great script below so  you can take and tweak it to suite your needs. This is a reasonable (and free) altarnative if you have only a few places that this needs to be implemented without too many addiotnal requirements. 


https://community-old.sisense.com/hc/en-us/community/posts/360039619893-Transposed-Pivot-Table-With-...

 

rapidbisupport
11 - Data Pipeline
11 - Data Pipeline

Hi @lalasisi ,

With some work, you could change style etc. with the BloX definition below to achieve transposed pivot table. YMMV - so i'd love to hear if this works for you. Please see the image and code below.

rapidbisupport_0-1721181567951.png

BloX Editor Code:

{
    "style": "#table-container66971f8dd755690032cc2e40 { font-family: Arial, Helvetica, sans-serif; border-collapse: collapse; width: 100%; } #table-container66971f8dd755690032cc2e40 td, #table-container66971f8dd755690032cc2e40 th { border: 1px solid #ddd; padding: 8px; } #table-container66971f8dd755690032cc2e40 tr:nth-child(even){background-color: #f2f2f2; } #table-container66971f8dd755690032cc2e40 tr:hover {background-color: #ddd; } #table-container66971f8dd755690032cc2e40 th { padding-top: 12px; padding-bottom: 12px; text-align: left; background-color: #04AA6D; color: white; }",
    "script": "",
    "title": "",
    "showCarousel": false,
    "body": [
        {
            "type": "Container",
            "style": {
                "padding": "5px"
            },
            "items": [
                {
                    "type": "TextBlock",
                    "id": "table-container66971f8dd755690032cc2e40",
                    "class": "table-container",
                    "text": "",
                    "style": {
                        "overflow": "auto"
                    }
                }
            ]
        }
    ],
    "actions": []
}

BloX Configuration Code:

{
    "fontFamily": "Open Sans",
    "fontSizes": {
        "default": 14,
        "small": 16,
        "medium": 20,
        "large": 50,
        "extraLarge": 32
    },
    "fontWeights": {
        "default": 500,
        "light": 100,
        "bold": 1000
    },
    "containerStyles": {
        "default": {
            "backgroundColor": "transparent",
            "foregroundColors": {
                "default": {
                    "normal": "#000000"
                },
                "white": {
                    "normal": "#ffffff"
                },
                "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": "white",
        "maxActions": 5,
        "spacing": "extraLarge",
        "buttonSpacing": 20,
        "actionsOrientation": "horizontal",
        "actionAlignment": "center",
        "showCard": {
            "actionMode": "inline",
            "inlineTopMargin": 16,
            "style": "default"
        }
    },
    "spacing": {
        "default": 5,
        "small": 20,
        "medium": 60,
        "large": 20,
        "extraLarge": 40,
        "padding": 0
    },
    "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,
    "imageBaseUrl": ""
}

BloX Widget Script Code:

widget.on('buildquery', (w, args) => {
    w.style.currentCard = {
        "style": `#table-container${w.oid} { font-family: Arial, Helvetica, sans-serif; border-collapse: collapse; width: 100%; } #table-container${w.oid} td, #table-container${w.oid} th { border: 1px solid #ddd; padding: 8px; } #table-container${w.oid} tr:nth-child(even){background-color: #f2f2f2; } #table-container${w.oid} tr:hover {background-color: #ddd; } #table-container${w.oid} th { padding-top: 12px; padding-bottom: 12px; text-align: left; background-color: #04AA6D; color: white; }`,
        "script": "",
        "title": "",
        "showCarousel": false,
        "body": [
            {
                "type": "Container",
                "style": {
                    "padding": "5px"
                },
                "items": [
                    {
                        "type": "TextBlock",
                        "id": `table-container${w.oid}`,
                        "class": "table-container",
                        "text": "",
                        "style": {
                            "overflow": "auto"
                        }
                    }
                ]
            }
        ],
        "actions": []
    }
})

widget.on('ready', (w, args) => {
	let rHeaders = []
	const table = document.createElement('table')
	
	for (i = 0; i < w.metadata.panel('Items').items.length; i++) {
		let tempHeaders = w.queryResult.map((res) => { return res[i].Text })
		// tempHeaders.reverse()
    	tempHeaders.unshift(w.metadata.panel('Items').items[i].jaql.title)
		// Create a header row
		let headerRow = table.insertRow()

		// Add header cells to the header row
		for (const header of tempHeaders) {
			let headerCell = headerRow.insertCell()
			headerCell.textContent = header
		}
	}
	
	let rValues = []
    let l = w.metadata.panel('Values').items.length + w.metadata.panel('Items').items.length
    for (let i = w.metadata.panel('Items').items.length; i < l; i++) {
        rValues.push(w.queryResult.map((res) => { return res[i].Text }))
    }
	
    rValues.forEach((a, i) => {
        // a.reverse()
        a.unshift(w.metadata.panel('Values').items[i].jaql.title)
    })
	
    // Create a row for each array of values
    for (const values of rValues) {
        const row = table.insertRow()

        // Add a cell for each value in the array
        for (const value of values) {
            const cell = row.insertCell()
            cell.textContent = value
        }
    }

    // Add the table to the document
    $(`#table-container${w.oid}`).empty()
    $(`#table-container${w.oid}`).append(table)
})
 
Alternatively - RAPID BI also has Adaptive Pivot Tables plugin, that could be used to achieve this (may be overkill for simple transpose though, but it allows viewer modification of the configuration of the table). Happy to discuss further - please reach out if you'd like to.
 
Let me know how you go?

 

 

@lee_hub above may be useful for you too - let me know?

 

DRay
Community Team Member
Community Team Member

Hello @lalasisi,

I'm following up to see if any of the solutions offered worked for you.

If so, please click the 'Accept as Solution' button on the appropriate post, that way other users with the same questions can find the answer. If not, please let us know so that we can continue to help.

Thank you.

David Raynor (DRay)

lalasisi
9 - Travel Pro
9 - Travel Pro

I still don't think it's a great way to paste some bespoke code behind the dashboard as a great solution. This is by far way more difficult to use compared to other dashboard platforms e.g. Tableau and PowerBI. I don't think it's fair to force me to "accept as solution". I'd like Sisense as a company to consider adding the drag and drop functionality. Let me know how I can reach the right person to communicate this through

DRay
Community Team Member
Community Team Member

Hi @lalasisi,

I completely understand. I'm not trying to "force" you to do anything. If you want to suggest this as a product enhancement, we have the Product Feedback Forum specifically for requests like that. Please submit your idea there so other Sisense users can add their votes and comments. Our product team uses that feedback to prioritize their efforts. Maybe we will see this added in a future release.

David Raynor (DRay)

lalasisi
9 - Travel Pro
9 - Travel Pro

Thanks for the link. I have submitted my idea