Sort expression not working

[meckeard]

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!

[Arthur_Fong]

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)

[meckeard]

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

[asinha1991]

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

[marcus_sommer · Accepted solution]

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

[meckeard]

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