Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Announcements

Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in.
**SIGN UP NOW**

- Qlik Community
- :
- Forums
- :
- Analytics
- :
- New to Qlik Analytics
- :
- Expression Sort MATCH with an IF expression inside

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

matthewp

Creator III

2019-06-12
04:54 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Expression Sort MATCH with an IF expression inside

I am trying to create a custom sort expression using an IF statement.

I have the following IF Expression:

=IF(VOTA >= -15 AND VOTA <= 15, '0',

IF(VOTA >= -60 AND VOTA <= -16, '-1 to 0',

IF(VOTA >= -120 AND VOTA <= -61, '-2 to -1',

IF(VOTA >= -180 AND VOTA <= -121, '-3 to -2',

IF(VOTA >= -240 AND VOTA <= -181, '-4 to -3',

IF(VOTA >= -241, '-4+',

IF(VOTA >= 60 AND VOTA <= 16, '1 to 0',

IF(VOTA >= 120 AND VOTA <= 61, '2 to 1',

IF(VOTA >= 180 AND VOTA <= 121, '3 to 2',

IF(VOTA >= 240 AND VOTA <= 181, '4 to 3',

IF(VOTA >= 241, '4+'

)))))))))))

Which Groups the field VOTA.

Then in a Sort Expression i have done this:

=MATCH((IF(VOTA >= -15 AND VOTA <= 15, '0',

IF(VOTA >= -60 AND VOTA <= -16, '-1 to 0',

IF(VOTA >= -120 AND VOTA <= -61, '-2 to -1',

IF(VOTA >= -180 AND VOTA <= -121, '-3 to -2',

IF(VOTA >= -240 AND VOTA <= -181, '-4 to -3',

IF(VOTA >= -241, '-4+',

IF(VOTA >= 60 AND VOTA <= 16, '1 to 0',

IF(VOTA >= 120 AND VOTA <= 61, '2 to 1',

IF(VOTA >= 180 AND VOTA <= 121, '3 to 2',

IF(VOTA >= 240 AND VOTA <= 181, '4 to 3',

IF(VOTA >= 241, '4+'

)))))))))))), '-4+', '-4 to -3', '-3 to -2', '-2 to -1', '-1 to 0', '0', '1 to 0','2 to 1','3 to 2','4 to 3','4+')

But it is still not sorting

- Tags:
- expression

1,108 Views

1 Reply

sunny_talwar

MVP

2019-06-12
08:40 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

How about if you change your if statement to this

```
=IF(VOTA >= -15 AND VOTA <= 15, Dual('0', 0),
IF(VOTA >= -60 AND VOTA <= -16, Dual('-1 to 0', -1),
IF(VOTA >= -120 AND VOTA <= -61, Dual('-2 to -1', -2),
IF(VOTA >= -180 AND VOTA <= -121, Dual('-3 to -2', -3),
IF(VOTA >= -240 AND VOTA <= -181, Dual('-4 to -3', -4),
IF(VOTA >= -241, Dual('-4+', -5),
IF(VOTA >= 60 AND VOTA <= 16, Dual('1 to 0', 1),
IF(VOTA >= 120 AND VOTA <= 61, Dual('2 to 1', 2),
IF(VOTA >= 180 AND VOTA <= 121, Dual('3 to 2', 3),
IF(VOTA >= 240 AND VOTA <= 181, Dual('4 to 3', 4),
IF(VOTA >= 241, Dual('4+', 5)
)))))))))))
```

and then sort this numerically.

1,085 Views

Community Browser