topic Re: hyperCube sorting issue in Integration, Extension & APIs

hyperCube sorting issue

edwin — Thu, 17 Nov 2022 18:41:47 GMT

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

Re: hyperCube sorting issue

alex_colombo — Mon, 21 Nov 2022 16:58:28 GMT

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 } ] }

Re: hyperCube sorting issue

edwin — Mon, 21 Nov 2022 19:29:51 GMT

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.

Re: hyperCube sorting issue

alex_colombo — Tue, 22 Nov 2022 07:55:16 GMT

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.

Re: hyperCube sorting issue

edwin — Tue, 22 Nov 2022 16:15:06 GMT

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

Re: hyperCube sorting issue

edwin — Wed, 30 Nov 2022 20:53:19 GMT

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?

Re: hyperCube sorting issue

alex_colombo — Thu, 01 Dec 2022 08:29:59 GMT

@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

Re: hyperCube sorting issue

edwin — Thu, 08 Dec 2022 14:05:52 GMT

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.

Re: hyperCube sorting issue

edwin — Mon, 27 May 2024 16:32:18 GMT

@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

Re: hyperCube sorting issue

alex_colombo — Wed, 29 May 2024 13:26:51 GMT

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..)?

Re: hyperCube sorting issue

edwin — Wed, 29 May 2024 16:46:37 GMT

hi Alex
thanks for the swift reply. the attached example has inter column sort order as 0,1,2,3. even the first dimension isnt sorting properly. the dimensions are sorted by default - other ascending:

{"qDef": {"qFieldDefs": ["province"]}, "qNullSuppression": true,"qOtherTotalSpec": {"qOtherMode": "OTHER_OFF","qSuppressOther": true,"qOtherSortMode": "OTHER_SORT_ASCENDING","qOtherCounted": {"qv": "5"},"qOtherLimitMode": "OTHER_GE_LIMIT"} }, {"qDef": {"qFieldDefs": ["region"]}, "qNullSuppression": true,"qOtherTotalSpec": {"qOtherMode": "OTHER_OFF","qSuppressOther": true,"qOtherSortMode": "OTHER_SORT_ASCENDING","qOtherCounted": {"qv": "5"},"qOtherLimitMode": "OTHER_GE_LIMIT"} }, {"qDef": {"qFieldDefs": ["city"]}, "qNullSuppression": true,"qOtherTotalSpec": {"qOtherMode": "OTHER_OFF","qSuppressOther": true,"qOtherSortMode": "OTHER_SORT_ASCENDING","qOtherCounted": {"qv": "5"},"qOtherLimitMode": "OTHER_GE_LIMIT"} }, { "qDef": {"qFieldDefs": ["indicator_code"]}, "qNullSuppression": true,"qOtherTotalSpec": {"qOtherMode": "OTHER_OFF","qSuppressOther": true,"qOtherSortMode": "OTHER_SORT_DESCENDING","qOtherCounted": {"qv": "5"},"qOtherLimitMode": "OTHER_GE_LIMIT"} } "qMeasures": [ {"qLabel": "Measure","qLibraryId": "LbjVGw", "qSortBy": {"qSortByState": 0,"qSortByFrequency": 0,"qSortByNumeric": 0,"qSortByAscii": 1,"qSortByLoadOrder": 0,"qSortByExpression": 0,"qExpression": {"qv": " "}} } ], "qSuppressZero": false, "qSuppressMissing": false, "qInterColumnSortOrder": [0,1,2,-3], "qStateName": "$","qMode": "P","qAlwaysFullyExpanded": true, "qNoOfLeftDims": 3 },tmp);

Re: hyperCube sorting issue

alex_colombo — Thu, 30 May 2024 12:51:24 GMT

No I'm sorry, I'm not following. You've uploaded an app with a pivot table with all dimensions sorted by default (it means your values will be sorted by ascii asc if they are strings, by numberically desc if they are numbers).

Why you are saying that first dimension is not sorted correctly? It is sorted by ascii asc correctly.

Please could you provide me the expected sorting behaviour with an example with data?