Sorting Chart By Different Field Than The One Displayed

[ewoytowitz]

I have a column chart that is currently displaying events on the x-axis (through an "eventCode field), and they are appearing automatically in descending order by the "total competitors" (displayed on the y-axis). I would like to sort and display the events on the x-axis instead by an "eventKey" field that is also in the Data Model. Is it possible to sort chart axis labels by a different field? Basically, I want to be able to replicate the custom sort configuration (see attachment) that is offered on the Sisense website in compose sdk.

[Benji_PaldiTeam]

Hello @ewoytowitz 

The script provided below enables you to sort the x-axis of a column chart based on another field.

Ensure to modify the variable 'newJaql' with the specific field details by which you want to sort, in your case, it's 'eventKey' and its corresponding table. Additionally, ensure that no two event codes have the same eventKey.

var newJaql = { jaql:{
			column: "Category ID",
			datatype: "numeric",
			dim: "[Category.Category ID]",
			merged: true,
			table: "Category",
			title: "ranking_field"
		}
	}
		
widget.on("beforequery", function (se, ev) {

	ev.query.metadata.push(newJaql)	

});

widget.on('queryend',function(se,ev){

	categoryPanelName = se.metadata.panels[0].items[0].jaql.title

	categoryResultIndex = ev.rawResult.headers.indexOf(categoryPanelName)
	rankingResultIndex = ev.rawResult.headers.indexOf(newJaql.jaql.title)

	var item_list = [];

	$.each(ev.rawResult.values, function(index, value){
		if(!(value[categoryResultIndex].text in item_list))
		{
			item_list[value[categoryResultIndex].text] = (value[rankingResultIndex].data == 'N\\A' ? -1 : value[rankingResultIndex].data)
		}
	})

	ev.rawResult.values.sort(function(a, b){
		var aIndex = item_list[a[0].data];
		var bIndex = item_list[b[0].data];

		if (aIndex < bIndex)
			return -1;
		if (aIndex > bIndex)
			return 1

			return 0;
	})

});

Feel free to reach out if you have further questions, we're always happy to help 🙂
[email protected] 
Paldi Solutions, Number #1 Sisense Plugins Developer

[steve · Accepted solution]

Hi @ewoytowitz 

I noticed your question was about sorting in Compose SDK, so offering an alternative solution.

It should be possible by first running the query, then after results are returned, choosing which columns in the result to map to the chart (e.g. not all of them).

There's an example of useExecuteQuery with sorting here https://www.sisense.com/platform/compose-sdk/playground/?example=queries%2Fuse-execute-query-sorting
It's sorted by Year, then by revenue.

You could imagine your eventCode as the year column in the example, with the sorting applied, and you only use the other columns in the chart mapping.

Example of mapping query results to a chart (instead of a table) here https://www.sisense.com/platform/compose-sdk/playground/?example=queries%2Fuse-execute-query

We will try to produce a more targeted example for this use case in future, thanks for the inspiration!

Steve

[DRay]

Hello @ewoytowitz,

I wanted to follow up to see if the solution offered by @Benji_PaldiTeam or @steve worked for you.

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

Thank you.

[DRay]

Hello @ewoytowitz,

I wanted to follow up to see if the solution offered by @Benji_PaldiTeam or @steve worked for you.

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

Thank you.