topic Re: Class Sorting in QlikView

Class Sorting

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

I am creating a calculated dimension using the following to determine the "No of Items per Script" class dimension:

=Dual(Left(Aggr(Class(Count([Drug Name]), 2), [Script Number]), FindOneOf(Aggr(Class(Count([Drug Name]), 2), [Script Number]), ' ',1))
& ' to ' &
(Num(Mid(Aggr(Class(Count([Drug Name]),2), [Script Number]),FindOneOf(Aggr(Class(Count([Drug Name]), 2), [Script Number]),' ',4)))-1), Left(Aggr(Class(Count([Drug Name]), 2), [Script Number]), FindOneOf(Aggr(Class(Count([Drug Name]), 2), [Script Number]), ' ',1))
& ' to ' &
(Num(Mid(Aggr(Class(Count([Drug Name]),2), [Script Number]),FindOneOf(Aggr(Class(Count([Drug Name]), 2), [Script Number]),' ',4)))-1))

My chart looks as follows:

I then sort by Ascending value based on "No of Items per Script" but the chart does not sort properly as shown above. Please provide a solution to this.

Regards.

Chris

Re: Class Sorting

prma7799 — Thu, 16 Nov 2017 07:59:06 GMT

Please share sample app...

or put your expression in Sort >> sort by expression >> asc

Re: Class Sorting

marcus_sommer — Thu, 16 Nov 2017 08:05:06 GMT

Try it without the dual() because class() is per default a dual-value - you could easily check it if you just wrap your class() with a num(). To change the interpretation you could adjust the 3. parameter of the function.

- Marcus

Re: Class Sorting

qliksus — Thu, 16 Nov 2017 08:37:20 GMT

Try putting the below expression in the Sorting tab under the Expression and sort by ascending

Left(Aggr(Class(Count([Drug Name]), 2), [Script Number]), FindOneOf(Aggr(Class(Count([Drug Name]), 2), [Script Number]), ' ',1))

Re: Class Sorting

Anonymous — Thu, 16 Nov 2017 08:48:20 GMT

Thank you PM. I changed the function to:

=Left(Aggr(Class(Count([Drug Name]), 2), [Script Number]), FindOneOf(Aggr(Class(Count([Drug Name]), 2), [Script Number]), ' ',1))
& ' to ' &
(Num(Mid(Aggr(Class(Count([Drug Name]),2), [Script Number]),FindOneOf(Aggr(Class(Count([Drug Name]), 2), [Script Number]),' ',4)))-1)

As mentioned by Marcus, class is a dual by default. I then using the above expression to sort by descending and i get the following:

There are still some anomalies on the sort order where the range 34 to 35 is misplaced.

Regards.

Chris

Re: Class Sorting

Anonymous — Thu, 16 Nov 2017 08:55:11 GMT

Thanks, Susant, most appreciated.

Regards.

Chris

Re: Class Sorting

Anonymous — Thu, 16 Nov 2017 08:56:32 GMT

Thank you Marcus for your contribution, most appreciated.

Regards.

Chris

Re: Class Sorting

antoniotiman — Thu, 16 Nov 2017 09:25:48 GMT

Hi Christopher,

if You want replace >= <= with 'to', then You can use Floor() function instead, like this

Regards,

Antonio

Re: Class Sorting

Anonymous — Thu, 16 Nov 2017 09:27:05 GMT

Thanks Antonio for your contribution.

Regards.

Chris