hyperCube sorting issue

[edwin]

Hi, i have a simple hyperCube with a couple of dimensions and measures.  i added a sorting number in the first column as there is a customized sort order depending on the dimensions being pulled.  the sort field is a unique number per row.

i first created a table in QS using the SORT column and its sorting as expected.

when i pulled the HC in a mashup, its not sorting accroding to the SORT column.  theis is my config:

{"qLabel": "TableSort","qLibraryId": "uGHpA","qNullSuppression": true,//"qOtherTotalSpec": {"qOtherMode": "OTHER_OFF","qSuppressOther": true,"qOtherCounted": {"qv": "5"},"qOtherLimitMode": "OTHER_GE_LIMIT"}, 
					"qSortCriterias": [{"qSortByState": 0,"qSortByFrequency": 0,"qSortByNumeric": 1,"qSortByAscii": 0,"qSortByLoadOrder": 0,}]},

ive tried a combination of sort by number and sort by ascii - doesnt work as expected,  in the create cube call, ive tried different combinations with the intercolumn sort order but i cant figure out what it is sorting by:

App.createCube({
            		"qInitialDataFetch": [{"qTop": TopRow,"qHeight": Height,"qWidth": (Dimensions.length + Measures.length)}],
            		"qDimensions": Dimensions,
            		"qMeasures":  Measures,
            		"qSuppressZero": false,"qSuppressMissing": false,"qMode": "S","qInterColumnSortOrder": [0],"qStateName": "$"

has anyone experienced this problem and solved it?  if you have, what did you do? 

thanks,

edwin

[alex_colombo]

Hi @edwin maybe it works in your QS table becaase you have manually modified the column order changing field position in Sort configuration?

Anyway, when I create Hypercubes I always specify sort order. In your qDef property add your sorting criteria for the first dimension and it should fix it. In below example I'm sorting my field with an ascending expression using a data moel field. If you want descending put -1 for qSortByExpression

"qDef": {
	"qFieldDefs": [
		"AsciiNum"
	],
	"qSortCriterias": [
		{
			"qSortByState": 0,
			"qSortByFrequency": 0,
			"qSortByNumeric": 0,
			"qSortByAscii": 0,
			"qSortByLoadOrder": 1,
			"qSortByExpression": 1,
			"qExpression": {
				"qv": "AsciiNum"
			},
			"qSortByGreyness": 0
		}
	]
}

[edwin]

Hi Alex,

thanks for the response.  perhaps you didnt ntice i do have that sort specified in my dimension:

{"qLabel": "TableSort","qLibraryId": "uGHpA","qNullSuppression": true,//"qOtherTotalSpec": {"qOtherMode": "OTHER_OFF","qSuppressOther": true,"qOtherCounted": {"qv": "5"},"qOtherLimitMode": "OTHER_GE_LIMIT"}, 
					"qSortCriterias": [{"qSortByState": 0,"qSortByFrequency": 0,"qSortByNumeric": 1,"qSortByAscii": 0,"qSortByLoadOrder": 0,}]},

the reason, i refer to the table is to express the fact that ive checked the sorting and that it works.  the table is the first thing i check and see how i want it sorted.

the werid thing is that i do have a few other hypercubes (although it is simpler than this particular one)and ive used the same sort spec and it is working.  its just in this one HC that sorting does not work.

[alex_colombo]

Hi @edwin , yes I see your sorting. Unfortunatly, without the app, I cannot tell you what is the reason why this numeric sorting is not working.

Please, try to force sorting with qSortByExpression, insert your numeric field in the expression and check what happen.

[edwin]

Hi Alex,
i didnt think to use expressions.  but i tried it just now and it didnt work.  i simplified the dimension objects and specified sorting in just that field, also tried a couple of combinations with autosort and expressions nothing worked.  i will take it up with management to share the mashup and application (we opened a ticket with support) but its slow going due to reluctance to share the app.  ill update this thread if we do get this resolved

[edwin]

Hi,
i just noticed that the hypercube is actually sorted by load order: qEelemntNumber. this is regardless if I add

 "qSortCriterias": [{
 	"qSortByNumeric": 1
 	//"qSortByExpression": 1,
	//"qExpression": {
	//	"qv": "num(fact_order_en)"
	//},
}]

on the first column. ive tried sort by ascii, sort by expression. it is still sorted by load order.

is there a way to override the sort by load order?

[alex_colombo]

@edwinit should be a way for doing this but, I'd like to have the app. Is there a way for upload the app with scrambled or fake data? I have to play with sorting directly in app for understanding the issue

[edwin]

Hi Alex, thanks for looking into this.  im working to re-cretae the error with a striped down app and different QVF.  ill post as soon as i finish it.

[edwin]

@alex_colombo 
Hi and it has been a while since i got back to this.  i was having a hard time getting approval to share our apps even when stripped down.
i did the easiest thing as the process was dragging - sort it in the mashup.  now that i have time for this, i decided to test this using random data and i was able to replicate my issue.  this time im using a pivot but i think the issue and solution should apply to straight tables as well. 

i have 3 dimensions and 1 measure, each dimension - i left sorting by default.  output is still sorted by load order i think - at least not what i was expecting.  ive attached both dashboard and mashup.  mashup only outputs to console for simplicity

thanks
edwin

[alex_colombo]

Hey @edwin what is the expected sorting for this pivot table? Which field do you want to sort and how (by numeric asc, by ascii desc, by expression, etc..)?