Sort all variables on x-axis

[TobiasJ]

Hello!

I am new to Qlik. I use Qlik Sense and have created an app where I have a chart with exposure (bars) and a KPI (line) and an x-axis.

Typically the axis has yearmonth which is an integer in the set {202301, 202302, 202303, ...} so I can track the KPI over time. The x-axis have a drop-down list with many more features (say N=100) and lets say one of them is called AREASIZE. These 100 features could be of type string or a numeric.

The data is loaded to Qlik via SAS, and in SAS the log says:
NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.

Doing proc contents in SAS on the dataset on the Qlik-server it still looks like there are some formats (the variable type in SAS is 1 or 2).

In Qlik, when I pick a variable like AREASIZE which (in sas) is an integer that can take values from 0 up to 500 then in Qlik it is sorted as {120,36, 63, 67, 20, ...}.

I can manually fix the sorting of each argument by going to "Data" pull whatever is in "BAR AND LINE" to "alternative dimension" and then pull AREASIZE to "BAR AND LINE" and then go to "SORTING" and pull "AREASIZE" as numer (1) in the list. However to do this process for all my variables (N=100) is very time consuming.

It would be very nice if QLIK could understand which variables that holds numeric info and by default sort these numerically. Is there are practical way of doing this when you have say 100 features (in particular I want them to be sorted on the x-axis of a chart)?

[Or]

This is typically handled in script, e.g.

Load Num#(AREASIZE) as AREASIZE

From YourTable;

Note that if the format of the number isn't "obvious", you may need to specify the format to read as a second parameter.

[Rohan]

Hi Tobias,

Have you tried writing a sorting expression which will toggle with the variable selected on your Monthyear Column ? Something like : Pick (Variable,expression1,expression2,.....,expressionN)

Regards,

Rohan.

[TobiasJ]

I changed in the load editor to:

LOAD
Num#(_myvar) as _myvar_num,
*;

and reloaded the data.

After the reload the variable "_myvar_num" appeared as "field" (so it is actually loaded). In editing mode I added it to the "alternative dimension" of the data of the chart.

In display mode I looked at the chart but it is not sorted in numeric order.

The sorting in the charts x-axis is {2,3,1,0,4,5,99,missing}.

So, hardcoding a variable as numeric in the load editor did not yield the result I was hoping for. The variable still appears strange sorted.

But I learned something, so thanks anyway!

Have you observed changed sort-order when hardcoding the format in data-load?

[TobiasJ]

Here is the same question you can say:

Solved: how to order alternative dimensions? - Qlik Community - 1214841

and the accepted solution is to do it "one-by-one" manually. If you have 100+ alternative dimension this is not a very nice method.

[TobiasJ]

Thanks for your replay. Can you give an example?

[Or]

Qlik typically sorts numerically by default for fields that are tagged as numeric. What is this 'missing'? If it's the text string 'missing', then obviously the field is not numeric...

You can check if the field is getting tagged correctly in the Data Model Viewer:

I can confirm that on my end, adding an Alternative that is tagged as Numeric to a combo chart and then switching to that Alternative sorts numerically as expected.

[TobiasJ]

I have hidden the name of variable in figure below, but it is $numeric $integer. However it is a lot of missing value (represented with . in SAS).

Num#(_myvar) as _myvar_num,

I will try to add a coalesce() in the data load editor, to transform the missing . to -1 (integer) and see if that helps. 

LOAD
Num#(Coalesce(_myvar,-1)) as _myvar_num,
*;

That gives the similar data model view except density is 100% and "." is not -1.

The new sorting order in the chart is {-1,2,3,1,0,4,5,99}. It appears to be the same order as the numbers occur (first time) in the data model view when reading rows 0,1,2,...

Maybe I am doing something wrong.

I note your data is 60 rows and mine is around 1M rows and say 300 columns. Maybe Qlik gives up on the sorting if there are more than N_something rows and M_something columns.

[TobiasJ · Accepted solution]

I have found no solution in Qlik to get variables on x-axis sorted (unless doing them manually). However workaround below can be implemented with 50 rows of sas-code.

An example variable is presented below (and it is numeric). I have hidden the name of variable in figure below, but it is $numeric $integer. However it is a lot of missing value (represented with . in SAS).

Num#(_myvar) as _myvar_num,

In the data load editor I tried to replace the missing with coalesce() to replace missing with -1. It worked but the sort order in the chart I use is for example {-1,2,3,1,0,4,5,99}. It appears to be the default sorting order is the same order as the numbers occur (first time) in the data model view when reading rows 0,1,2,...

Work around in SAS:
Because Qlik sorts in order they appear... lets make them appear in the order we want!

I do proc contents in SAS on my dataset and then loop through all different names in the dataset. For each of these I check if the number of distinct values is less than 1000. If less than 1000 I assume it is a variable that I want sorted in Qlik and I add it to a dummy dataset in SAS.

The dummy data would look something like this:

Dummy1   Row    MyVar1         MyVar2       MyVar3     ....
1                      1           0                     No                 1
1                      2           1                     Yes                2
1                      3           2                                            3
1                      4           3                                            4
1                      5           4                                            5
1                      6           5                                            6
1                      7           99                                          7
1                      8                                                          8
1                      9                                                          9
1                      10                                                       10
...

Then I add this dummy data to the SAS-data:

data final;
set dummy_data original_data;
run;

By doing this now the order looks nice in Qlik.