topic Re: Sort expression not working in QlikView

Sort expression not working

meckeard — Wed, 25 Nov 2020 16:16:04 GMT

All,

I have a sort expression not working. Nothing I do changes the sort. Attached is a picture of my chart with the properties open and showing the sort expression but I'll explain in detail.

My chart counts how many customers are in the following average monthly balance buckets and here is the dimension:

=if(AverageBalance <= 0, 'Negative',
if(AverageBalance >= 0 and AverageBalance <= 50, '$0-$50',
if(AverageBalance >= 51 and AverageBalance <= 500, '$51-$500',
if(AverageBalance >= 501 and AverageBalance <= 1000, '$501-$1000',
if(AverageBalance >= 1001 and AverageBalance <= 5000, '$1001-$5000',
if(AverageBalance >= 5001 and AverageBalance <= 10000, '$5001-$10000',
'Over $10000'))))))

I need the values/buckets to sort in the above order and here is the sort expression that isn't working:

=match([Avg Balance], 'Negative', '$0-$50', '$51-$500', '$501-$1000', '$1001-$5000', '$5001-$10000', 'Over $10000')

I've tried using the field name (AverageBalance), the label of the dimension (Avg Balance) and many other things but it's not working.

What am I doing wrong?

Thanks!

Re: Sort expression not working

Arthur_Fong — Wed, 09 Oct 2019 00:36:39 GMT

Try this in sort expression:

pick(match([Avg Balance], 'Negative', '$0-$50', '$51-$500', '$501-$1000', '$1001-$5000', '$5001-$10000', 'Over $10000'),

1,2,3,4,5,6,7)

Re: Sort expression not working

meckeard — Thu, 10 Oct 2019 11:30:28 GMT

Hi jkfog93,

Thanks for helping.

That didn't work. In fact, I can put in an invalid field name in place of [Avg Balance] and the sort remains the same. It's almost as if it is completely ignoring any expression I use. The "Expression" checkbox is checked, so I don't know why I can't change the sort order.

Mark

Re: Sort expression not working

asinha1991 — Thu, 10 Oct 2019 12:30:03 GMT

match should have worked, can you share screenshot of how you are putting it?

Re: Sort expression not working

marcus_sommer — Thu, 10 Oct 2019 13:40:18 GMT

Each invalid expression respectively with invalid results will be ignored. In your case the issue is caused through your trial to apply the sorting on your calculated dimension - this couldn't work.

But using just: avg(AverageBalance) should work. Alternatively you could create your buckets as dual-values, like:

=if(AverageBalance <= 0, dual('Negative', 1),
if(AverageBalance >= 0 and AverageBalance <= 50, dual('$0-$50', 2),
if(AverageBalance >= 51 and AverageBalance <= 500, dual('$51-$500', 3),
if(AverageBalance >= 501 and AverageBalance <= 1000, dual('$501-$1000', 4),
if(AverageBalance >= 1001 and AverageBalance <= 5000, dual('$1001-$5000', 5),
if(AverageBalance >= 5001 and AverageBalance <= 10000, dual('$5001-$10000', 6),
dual('Over $10000', 7)))))))

and then the sorting could be just numeric.

- Marcus

Re: Sort expression not working

meckeard — Thu, 10 Oct 2019 13:59:38 GMT

Marcus - I wasn't aware a calculated dimension couldn't be sorted like this. I changed it to match your example using the dual function/method and sorting by the number and it worked. Thanks!

All - thank you very much for all the assistance. It's greatly appreciated.

Mark