Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Class Sorting

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:


Items.JPG

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

1 Solution

Accepted Solutions
qliksus
Specialist II
Specialist II

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

View solution in original post

8 Replies
prma7799
Master III
Master III

Please share sample app...

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

marcus_sommer

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

qliksus
Specialist II
Specialist II

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

Anonymous
Not applicable
Author

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:

Items2.JPG

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

Regards.

Chris

Anonymous
Not applicable
Author

Thanks, Susant, most appreciated.

Regards.

Chris

Anonymous
Not applicable
Author

Thank you Marcus for your contribution, most appreciated.

Regards.

Chris

antoniotiman
Master III
Master III

Hi Christopher,

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

Regards,

Antonio

Anonymous
Not applicable
Author

Thanks Antonio for your contribution.

Regards.

Chris