# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Coming Aug. 9: New Simplified Authoring for Qlik Sense SaaS – For Details, CLICK HERE
cancel
Showing results for
Did you mean:
Creator

## If statment with a lower and upper limit

Hi everyone,

I was hoping to pick your brains to understand why the following code does not work

I have a column with multiple values which I would like to categorise into buckets according to value.

if(num(value) < num(0) and num(value)    >= num(10), '<£10',
if(num(value) <= num(10) and num(value)  >= num(50), '£10 - £50',
if(num(value) <= num(50) and num(value)  >= num(100), '£50 - £100',
if(num(value) <= num(100) and num(value) >= num(499), '£100 - £499',
if(num(value) <= num(500) and  num(value)  >= num(999), '£500 - £999',
if(num(value) <= num(1000) and num(value)  >= num(5000), '£1k - £5k',
if(num(value) <= num(5000) and  num(value  >= num(10000), '£5k - £10k',
if(num(value) <= num(10000) and num(value) >= num(50000), '£10k - £50k',
if(num(value) <= num(50000) and num(value) >= num(100000), '£50k - £100k',
if(num(value) <= num(100000) and num(value)  >= num(500000), '£100k - £500k',
if(num(value) <= num(1000000) and num(value) >= num(10000000), '1M – 10M',
if(num(value) > num(10000000) and num(value  >= num(900000000000000000000000000), '>10m')))))))))))) as [SumGroupings]

However the result is usually '>10m' even though the column value has a vast range of values from very small to very large figures.

Would anyone happen to know why this is happening?

Thanks,
Isaac

1 Solution

Accepted Solutions
MVP

And you might be able to do away with num() functions all together

if(value > 0 and value <= 10, '<£10',

if(value > 10 and value <= 50, '£10 - £50',

if(value > 50 and value <= 100, '£50 - £100',

if(value > 100 and value <= 499, '£100 - £499',

if(value > 500 and  value <= 999, '£500 - £999',

if(value > 1000 and value <= 5000, '£1k - £5k',

if(value > 5000 and  value <= 10000, '£5k - £10k',

if(value > 10000 and value <= 50000, '£10k - £50k',

if(value > 50000 and value <= 100000, '£50k - £100k',

if(value > 100000 and value <= 500000, '£100k - £500k',

if(value > 1000000 and value <= 10000000, '1M – 10M',

if(value > 10000000 and value <= 900000000000000000000000000, '>10m')))))))))))) as [SumGroupings]

4 Replies
MVP

I think you < and >= signs are incorrect... try this

if(num(value) > num(0) and num(value) <= num(10), '<£10',
if(num(value) > num(10) and num(value) <= num(50), '£10 - £50',
if(num(value) > num(50) and num(value) <= num(100), '£50 - £100',
if(num(value) > num(100) and num(value) <= num(499), '£100 - £499',
if(num(value) > num(500) and  num(value) <= num(999), '£500 - £999',
if(num(value) > num(1000) and num(value) <= num(5000), '£1k - £5k',
if(num(value) > num(5000) and  num(value) <= num(10000), '£5k - £10k',
if(num(value) > num(10000) and num(value) <= num(50000), '£10k - £50k',
if(num(value) > num(50000) and num(value) <= num(100000), '£50k - £100k',
if(num(value) > num(100000) and num(value) <= num(500000), '£100k - £500k',
if(num(value) > num(1000000) and num(value) <= num(10000000), '1M – 10M',
if(num(value) > num(10000000) and num(value) <= num(900000000000000000000000000), '>10m')))))))))))) as [SumGroupings]

MVP

And you might be able to do away with num() functions all together

if(value > 0 and value <= 10, '<£10',

if(value > 10 and value <= 50, '£10 - £50',

if(value > 50 and value <= 100, '£50 - £100',

if(value > 100 and value <= 499, '£100 - £499',

if(value > 500 and  value <= 999, '£500 - £999',

if(value > 1000 and value <= 5000, '£1k - £5k',

if(value > 5000 and  value <= 10000, '£5k - £10k',

if(value > 10000 and value <= 50000, '£10k - £50k',

if(value > 50000 and value <= 100000, '£50k - £100k',

if(value > 100000 and value <= 500000, '£100k - £500k',

if(value > 1000000 and value <= 10000000, '1M – 10M',

if(value > 10000000 and value <= 900000000000000000000000000, '>10m')))))))))))) as [SumGroupings]

MVP & Luminary

Another and even easier and more performant approach would be to use a pick(match()) for it like:

= pick(match(-1, value < 10, value < 50, ...), '<10', '<50', ...)

- Marcus

ps: the origin idea to compare conditions against TRUE is from Sunny.

Creator
Author

You're right, it was my greater than smaller than logic that was incorrect. Thank you for your help, wish I asked this earlier instead of spending days trying to figure out why its not working