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

- 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,153 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,130 Views

Community Browser