topic Re: Making a custom sort filter with a calculated dimension in QlikView

Making a custom sort filter with a calculated dimension

Mon, 13 Apr 2015 16:21:31 GMT

The title is pretty self explanatory.

I have the below calculated dimension. I want to compare jeans waist sizes to top sizes. So I match jeans waist sizes (ARTIKEL_MAAT_ID) 28,29 and 30 to size small for example. Below I have made all the matches. But now I want to make a custom sort order. So despite the outcome of the sum, I want to go from Small -> 3XL. Anyone has some advice on how to do this?

Much appreciated,

Maarten

=if(Match(ARTIKEL_MAAT_ID, '28', '29', '30'), ('SMALL'') & Chr(10)& '(28, 29, 30)' ) &

if(Match(ARTIKEL_MAAT_ID, '31', '32', '33'), ('MEDIUM'')& Chr(10)& '(31, 32, 33)') &

if(Match(ARTIKEL_MAAT_ID, '34', '35'), ('LARGE')& Chr(10)& '(34, 35)' )&

if(Match(ARTIKEL_MAAT_ID, '36'), ('XL')& Chr(10)& '(36)' ) &

if(Match(ARTIKEL_MAAT_ID, '38'), ('2XL'')& Chr(10)& '(38)' )&

if(Match(ARTIKEL_MAAT_ID, '40','42'), ('3XL'')& Chr(10)& '(40, 42)' )

Re: Making a custom sort filter with a calculated dimension

ramoncova06 — Mon, 13 Apr 2015 16:25:57 GMT

you can dual to set the number value of the size and sort it that way

=if(Match(ARTIKEL_MAAT_ID, '28', '29', '30'), dual(('SMALL'') & Chr(10)& '(28, 29, 30)' ),1) &

if(Match(ARTIKEL_MAAT_ID, '31', '32', '33'), dual(('MEDIUM'')& Chr(10)& '(31, 32, 33)'),2) &

if(Match(ARTIKEL_MAAT_ID, '34', '35'), dual(('LARGE')& Chr(10)& '(34, 35)' ),3)&

if(Match(ARTIKEL_MAAT_ID, '36'), dual(('XL')& Chr(10)& '(36)' ),4 &

if(Match(ARTIKEL_MAAT_ID, '38'), dual(('2XL'')& Chr(10)& '(38)' ),5)&

if(Match(ARTIKEL_MAAT_ID, '40','42'), dual(('3XL'')& Chr(10)& '(40, 42)',6)

Re: Making a custom sort filter with a calculated dimension

Colin-Albert — Mon, 13 Apr 2015 16:35:22 GMT

As RamonCova06 has suggested, DUAL will allow you to show sizes in the correct order.

There is a good explanation of using dual here

How to use- Dual()

Re: Making a custom sort filter with a calculated dimension

stigchel — Mon, 13 Apr 2015 16:43:21 GMT

I think you can just sort on the dimension by using an expression

=ARTIKEL_MAAT_ID

=Num(ARTIKEL_MAAT_ID)

if this is text

And should your calculated dimension not be more like this?

=if(Match(ARTIKEL_MAAT_ID, '28', '29', '30'), '(SMALL)' & Chr(10) & '(28, 29, 30)' ,

if(Match(ARTIKEL_MAAT_ID, '31', '32', '33'), '(MEDIUM)'& Chr(10)& '(31, 32, 33)' ,

if(Match(ARTIKEL_MAAT_ID, '34', '35'), '(LARGE)'& Chr(10)& '(34, 35)',

if(Match(ARTIKEL_MAAT_ID, '36'), '(XL)' & Chr(10)& '(36)',

if(Match(ARTIKEL_MAAT_ID, '38'), '(2XL)'& Chr(10)& '(38)',

if(Match(ARTIKEL_MAAT_ID, '40','42'), '(3XL)'& Chr(10)& '(40, 42)'

))))))

Re: Making a custom sort filter with a calculated dimension

Tue, 14 Apr 2015 07:34:06 GMT

Thank you all for the help. Correct answer:

=if(Match(ARTIKEL_MAAT_ID, '28', '29', '30'), DUAL('SMALL' & Chr(10)& '(28, 29, 30)' ,1),

if(Match(ARTIKEL_MAAT_ID, '31', '32', '33'), DUAL('MEDIUM'& Chr(10)& '(31, 32, 33)' ,2),

if(Match(ARTIKEL_MAAT_ID, '34', '35'), DUAL('LARGE'& Chr(10)& '(34, 35)' ,3),

if(Match(ARTIKEL_MAAT_ID, '36'), DUAL('XL'& Chr(10)& '(36)' ,4),

if(Match(ARTIKEL_MAAT_ID, '38'), DUAL('2XL'& Chr(10)& '(38)' ,5),

if(Match(ARTIKEL_MAAT_ID, '40','42'), DUAL('3XL'& Chr(10)& '(40, 42)' ,6)))))))

If I would only use the & sign to connect the lines it would only show the first one.