Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
chitemerere
Specialist
Specialist

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
MVP & Luminary
MVP & Luminary

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

chitemerere
Specialist
Specialist
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

chitemerere
Specialist
Specialist
Author

Thanks, Susant, most appreciated.

Regards.

Chris

chitemerere
Specialist
Specialist
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

chitemerere
Specialist
Specialist
Author

Thanks Antonio for your contribution.

Regards.

Chris