# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
New Contributor III

## Can't figure out how to sort by expression

Hello everyone!

So I'm trying to sort the dimension of a pivot table in a very specific order and I can't seem to quite get what I need. Here's the expression I have so far:

```=If(tblArticleMaster_SectAct = 'M10' or tblArticleMaster_SectAct = 'M11' or tblArticleMaster_SectAct = 'M15' or tblArticleMaster_SectAct = 'M16' or tblArticleMaster_SectAct = 'M20' or tblArticleMaster_SectAct = 'M25' or tblArticleMaster_SectAct = 'M65',1,
If(tblArticleMaster_SectAct = 'M30' or tblArticleMaster_SectAct = 'M35',2,
If(tblArticleMaster_SectAct = 'M50' or tblArticleMaster_SectAct = 'M57',4,
If(tblArticleMaster_SectAct = 'M60',5,
If(tblArticleMaster_SectAct = 'M40',3,
If(tblArticleMaster_SectAct = 'M00' or tblArticleMaster_SectAct = 'M45' or tblArticleMaster_SectAct = 'M55' or tblArticleMaster_SectAct = 'M70' or tblArticleMaster_SectAct = 'M99',6,
If(tblArticleMaster_SectAct = 'Other',7))))))) ```

When I order by Ascending, I get the order 4-1-6-2-3-5. When I order by Descending, I get the order 5-3-4-1-6-2. What am I doing wrong here? I'm guessing my expression probably has an error..

Any help would be greatly appreciated!

Thank you,

LesJean

Labels (3)

• ### Sort by expression

1 Solution

Accepted Solutions
MVP

## Re: Can't figure out how to sort by expression

```=Match(
If(tblArticleMaster_SectAct = 'M10' or tblArticleMaster_SectAct = 'M11' or tblArticleMaster_SectAct = 'M15' or tblArticleMaster_SectAct = 'M16' or tblArticleMaster_SectAct = 'M20' or tblArticleMaster_SectAct = 'M25' or tblArticleMaster_SectAct = 'M65', 'Produits parentéraux',
If(tblArticleMaster_SectAct = 'M30' or tblArticleMaster_SectAct = 'M35', 'Produits Oto-Rhino-Ophtalmiques',
If(tblArticleMaster_SectAct = 'M50' or tblArticleMaster_SectAct = 'M57', 'Préparations orales',
If(tblArticleMaster_SectAct = 'M60', 'Préparations topiques',
If(tblArticleMaster_SectAct = 'M40', 'Suppositoires',
If(tblArticleMaster_SectAct = 'M00' or tblArticleMaster_SectAct = 'M45' or tblArticleMaster_SectAct = 'M55' or tblArticleMaster_SectAct = 'M70' or tblArticleMaster_SectAct = 'M99', 'Produits divers',
If(tblArticleMaster_SectAct = 'Other', 'Non attribué')))))))
, 'Produits parentéraux'
, 'Produits Oto-Rhino-Ophtalmiques', 'Suppositoires',
, 'Préparations orales'
, 'Préparations topiques'
, 'Produits divers'
, 'Non attribué')```
5 Replies
Contributor

## Re: Can't figure out how to sort by expression

How about using the MATCH function?

https://help.qlik.com/en-US/sense/February2019/Subsystems/Hub/Content/Sense_Hub/Scripting/Conditiona...

match( tblArticleMaster_SectAct, 'M10', 'M11', 'M15', 'M16', 'M20', 'M25', 'M65', 'M30', 'M35', 'M40', 'M50', 'M57', 'M60', 'M00', 'M45', 'M55', 'M70', 'M99', 'Other')

Then the returning value would be lower towards the left side of the list.

On a side note - put a new dimension in your PIVOT, with your IF-sentence to see what's actually evaluated for each value.

New Contributor III

## Re: Can't figure out how to sort by expression

Your suggestion doesn't seem to quite work. I'll give more details as it may help resolving my situation.

Here's the code of the dimension I'm trying to sort:

```=If(tblArticleMaster_SectAct = 'M10' or tblArticleMaster_SectAct = 'M11' or tblArticleMaster_SectAct = 'M15' or tblArticleMaster_SectAct = 'M16' or tblArticleMaster_SectAct = 'M20' or tblArticleMaster_SectAct = 'M25' or tblArticleMaster_SectAct = 'M65', 'Produits parentéraux',
If(tblArticleMaster_SectAct = 'M30' or tblArticleMaster_SectAct = 'M35','Produits Oto-Rhino-Ophtalmiques',
If(tblArticleMaster_SectAct = 'M50' or tblArticleMaster_SectAct = 'M57', 'Préparations orales',
If(tblArticleMaster_SectAct = 'M60', 'Préparations topiques',
If(tblArticleMaster_SectAct = 'M40', 'Suppositoires',
If(tblArticleMaster_SectAct = 'M00' or tblArticleMaster_SectAct = 'M45' or tblArticleMaster_SectAct = 'M55' or tblArticleMaster_SectAct = 'M70' or tblArticleMaster_SectAct = 'M99', 'Produits divers',
If(tblArticleMaster_SectAct = 'Other', 'Non attribué'))))))) ```

As you can see, I'm basically regrouping different SectAct into groups that I'm using as the dimension.

So I tried using the Match function you've given me and here's the sorting order I got:

4-1-6-2-3-4

I also tried using these variations of your formula

`=Match(tblArticleMaster_SectAct, 'M10' or 'M11' or 'M15' or 'M16' or 'M20' or 'M25' or 'M65', 'M30' or 'M35', 'M40', 'M50' or 'M57', 'M60', 'M00' or 'M45' or 'M55' or 'M70' or 'M99', 'Other')`
`=Match(tblArticleMaster_SectAct,'Produits parentéraux', 'Produits Oto-Rhino-Ophtalmiques', 'Suppositoires', 'Préparations orales', 'Préparations topiques', 'Produits divers', 'Non attribué')`

With the first one I got a sorting order of 4-1-6-5-2-3 and I also got 4-1-6-5-2-3 with the second one.

I tried your suggestion of using my original formula inside a separate dimension to check to numbers returned and I do get the correct values. Sorting numerically ascending on this new dimensions gets me the exact order I need, but I can't use this technique as I need this pivot table to look a certain way and adding an additional dimension makes the table not as aesthetically pleasing.

I hope we can figure this out.

Contributor

## Re: Can't figure out how to sort by expression

I have a hard time to figure out your exact wish. Is it possible to see some test data or a screenshot?

Do you have other dimensions in your SORT-properties? Maybe it's as simple as moving one above the other in order?

MVP

## Re: Can't figure out how to sort by expression

```=Match(
If(tblArticleMaster_SectAct = 'M10' or tblArticleMaster_SectAct = 'M11' or tblArticleMaster_SectAct = 'M15' or tblArticleMaster_SectAct = 'M16' or tblArticleMaster_SectAct = 'M20' or tblArticleMaster_SectAct = 'M25' or tblArticleMaster_SectAct = 'M65', 'Produits parentéraux',
If(tblArticleMaster_SectAct = 'M30' or tblArticleMaster_SectAct = 'M35', 'Produits Oto-Rhino-Ophtalmiques',
If(tblArticleMaster_SectAct = 'M50' or tblArticleMaster_SectAct = 'M57', 'Préparations orales',
If(tblArticleMaster_SectAct = 'M60', 'Préparations topiques',
If(tblArticleMaster_SectAct = 'M40', 'Suppositoires',
If(tblArticleMaster_SectAct = 'M00' or tblArticleMaster_SectAct = 'M45' or tblArticleMaster_SectAct = 'M55' or tblArticleMaster_SectAct = 'M70' or tblArticleMaster_SectAct = 'M99', 'Produits divers',
If(tblArticleMaster_SectAct = 'Other', 'Non attribué')))))))
, 'Produits parentéraux'
, 'Produits Oto-Rhino-Ophtalmiques', 'Suppositoires',
, 'Préparations orales'
, 'Préparations topiques'
, 'Produits divers'
, 'Non attribué')```
New Contributor II

## Re: Can't figure out how to sort by expression

This is the solution i used when i had a similar problem