Can't figure out how to sort by expression

[LesJean]

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

[hacr]

How about using the MATCH function?

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

If you list in your given order of your IF sentence.

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.

[LesJean]

Thank you for your quick answer mate.

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.

Thanks again for your suggestions!

[hacr]

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?

[sunny_talwar · Accepted solution]

How about this

=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é')

[ben_skerrett]

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