Sort By Expression on calculated dimension

[AbhijitBansode]

Hi,

I want to apply custom sort on calculated dimension in chart.

but, i came to know from reference manual, Sort By Expession does not work on calculated dimension.

- Is their any other way to achieve this?

Below e.g. illustrates my requirement in detail.

Two fields viz., 'Product' and 'Sub Product' need to be shown in the chart depending upon selection in the respective fields.

Hence calcualted dimension is given in chart (not able to use more than one chart because of other reasons).

- Can we give predefined sort order at loading level?

e.g.If I want to view values of field product in below sort order

'J*','C*','B*','O*','*'

how to achieve this at the time of loading the data in qlikview?

[brenner_martina]

Hi, Abhijit,

yes, you can use an expression for sorting like this:

If(Field='abc',1, If(Field='aabd',2, If(Field='bcd',3, ...,N )))

or another way is to enter an inlinetable with sorted order before loading the data from the table.

[gauravkhare]

Hi Abhijit,

One easy to achieve this sorting is done by using wildmatch. Just go through reference manual or help and read about wildmatch.

It will surely 100% work fine.

[erichshiino]

Hi, I'd say that you defined strings as the dimension values.

You could use dual on the creation of the calculated dimension.

For example, if you write something like this:

= aggr( if(sum(value)<100, 'LOW', IF(SUM(VALUE<200),'MEDIUM' ... // AND SO ON

Instead of 'LOW' you could right

= aggr( if(sum(value)<100, DUAL('LOW', 1) ', IF(SUM(VALUE<200),DUAL('MEDIUM',2) ... // AND SO ON

It means QlikView should show the string 'LOW' but see this internally as the number one. Thus, you can sort the dimension by NUMERIC VALUE (ascending or descending).

Hope it helps.

If you need more help, we can post your calculated dimension expression.

[AbhijitBansode]

Thank you all for reply,

there are some problems related to suggestions given by you:

i can not use inline table because, there are not exact number of records every time ,

since sorting is done using wildmatch character, inline wizard does not allow me to use wild characters.

e.g.

Inline

Load[

'J*','C*','O*','A*'

];

Inline wizard treats 'J*' as string not as value staring with 'J'.

Again,I'd tried using wildmatch() function at report level, but it is not working with calculated dimension.

Dual function can be used to assigning numeric value to individual string but,my requirement is to assign

numeric values to field values.

I've attached a sample qvw file with this.. please help me on the same.

Below is the required sorting order that need to be implemented in the application:

'O*','S*','M*','J*','*'.

[brenner_martina]

Hi, Abhijit,

look the attached file, does it solve your problem?

[AbhijitBansode]

thanks for quick reply Martina.
again, the approach provided by you works well on single field/dimension.
because in this approach also, we need to use 'Sort By Expression' and this type
of sort order does not work with calculated dimension.

[brenner_martina]

Hi Abhijit,

here is a file again, here I entered the sort order with an expression like my first answer

[AbhijitBansode]

Martina,thank you very very much for reply.

but, my problem is still not resolved

here is the sort order entered in Sort by Expression in bar chart:

If(MainProduct Like 'J*',1,

If(MainProduct Like 'C*',2,

If(MainProduct Like 'B*',3,

If(MainProduct Like 'O*',4,

If(MainProduct Like 'S*',5,

If(MainProduct Like 'M*',6,99))))))

This means.. my bar chart should show values in the order of
JP,CS,ORA1,SP,MF
but,it is showing values in the order of
CS,JP,MF,ORA1,SP
even though sort by expression is given.

...
In table box sorting work fine because there is no calculated field in it.

I guess, above is not working because sort by expression does not work on calculated dimension.

Any alternative for this(without using different charts)?

[#10 · Accepted solution]

Hi Abhijit,

I believe that the confusion here is due to the fact that you are running a fairly old release of QlikView 9 (9.00.7314).
From Version 9 SR5 this behavior has changed and it is now possible to sort a calculated dimension by an expression.

I would recommend that you download SR7 (or version 10 SR2) from www.qlik.com/download and this should resolve the issue.